Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 4 hours 47 min ago

Data Guard vs Active Data Guard

Thu, 2017-12-07 21:46
Hi, Please could i get some answers for the following: What is the difference between DG and ADG ? What are the benefits of DG ? What are the benefits of ADG ? What are the risks / constraints of using a DR environment for near real ti...
Categories: DBA Blogs

Grouping Id

Thu, 2017-12-07 01:06
Hi Tom I never really understood the usage of GROUPING_ID function in OTN.I heard it avoids using multiple GROUPING functions.Can you please illustrate with a small example Thanks
Categories: DBA Blogs

Dynamic Sql to get the value of the column which is formed by concatenating two strings.

Mon, 2017-12-04 18:06
Hi Team, I have a query like this I will get the column name at run time something like IF conditions 1 then Column A. IF conditions 2 then Column B. IF conditions 3 then Column C. IF conditions 4 then Column D. Once i get to know whi...
Categories: DBA Blogs

I dropped a table in oracle but when i saw the indexes became like 'BIN$...' i rebuild them the state is still VALID

Sun, 2017-12-03 23:46
I dropped the table with cascade option, after importing the table the indexes are there with BIN$... name and the state is VALID. Are they really valid i try to rebuild its rebuilding but name is not changing.
Categories: DBA Blogs

Forms Builder won't connect to database - ORA 28040

Fri, 2017-12-01 16:46
I've installed an Oracle 12.2 database on my PC running Windows 10. That seems to be fine and I can connect to the database and the sample HR database is all there. I have then installed the Oracle Developer Suite v10.1.2. That installation appears t...
Categories: DBA Blogs

Service and module

Fri, 2017-12-01 16:46
Dear Team, Hope doing well..!!!! please help me to understand difference between service and module in oracle database. Whats use of module? Thanks Pradeep
Categories: DBA Blogs

Oracle provider for OLE DB (OraOLEDB) 11.2.0.1.0 unable to connect to Oracle DB 10 Release 2

Thu, 2017-11-30 22:26
I have installed Oracle provider for OLE DB (OraOLEDB) 11.2.0.1.0 on a server to allow our SIEM to connect to our customer's Oracle DB 10G R2 for monitoring purpose. However, I'm still getting error saying "ORA-12541: TNS:no listener" when I test...
Categories: DBA Blogs

pragma autonomous_transaction; and database links

Thu, 2017-11-30 22:26
I have a package of functions that return data from a SqlServer database through a link. Usually the results are just displayed in optional fields on a web page or client program. They take the form of: <code> function get_info(ar_key number) ...
Categories: DBA Blogs

How can i read a csv file

Wed, 2017-11-29 09:46
Hi TOM :) Resourse: 1) i have a table that was milions of records of the clients 2) and i have a CSV with only 1,200 clients 3) i don't have permitions to create a table. Problem: how can i read from the CSV to join with the pr...
Categories: DBA Blogs

UTL_FILE or EXTERNAL Table operation on files in Application Server

Tue, 2017-11-28 15:26
Hi Tom, We are running Oracle EBS R12.2.6. We have application server and database server hosted on two separate physical servers. They are not having any shared file locations. We are receiving the inbound files from a third party system in...
Categories: DBA Blogs

Validate constraint recursively run a SELECT with an "ordered" hint

Tue, 2017-11-28 15:26
I have been looking into minimizing the time it takes to validate a referential constraint. We want it to be validated for the optimizer to utilize join elimination during query rewrite. However, for a big table it takes a lot of time to validate ? a...
Categories: DBA Blogs

Oracle Live SQL

Mon, 2017-11-27 21:06
Hello, Is there a way I could run explain plan on Oracle Live SQL? Apparently, it gives an error that PLAN_TABLE doesn't exist. I ran utlxplan.sql in my session but still could not execute statement SELECT * FROM TABLE(DBMS_XPLAN.display) W...
Categories: DBA Blogs

Data Compression and Maintenance after archival

Mon, 2017-11-27 21:06
Hi , We have implemented archival solution for a client and now exploring further actions. Initial data size was around 25 TB's with major tables partitioned monthly. After archival, the size of active DB is around 13 TB's and archival DB is aroun...
Categories: DBA Blogs

Dynamic Select with Cursor type

Mon, 2017-11-27 21:06
How can i get value from sql using dbms_sql type of CURSOR ....COL_TYPE=102 <code>DECLARE RUN_S CLOB; IGNORE NUMBER; SOURCE_CURSOR NUMBER; PWFIELD_COUNT NUMBER DEFAULT 0; L_DESCTBL DBMS_SQL.DESC_TAB2; Z...
Categories: DBA Blogs

sys password change and orapwd file

Mon, 2017-11-27 02:46
Hi Tom, What is the relation between SYS user password and orapwd file ? What are the various methods to change the SYS user password. If I change the SYS user password using from sqlplus use password command or use alter user ...
Categories: DBA Blogs

SYS, SYSDBA, SYSOPER, SYSTEM

Mon, 2017-11-27 02:46
Hi I am very new to oracle. I have installed Oracle 10g compatible with windows vista. I am confused with what are exactly, SYS, SYSDBA, SYSOPER and SYSTEM? How they differ and what is the specific purpose of these automatically created accounts w...
Categories: DBA Blogs

email in PL/SQL

Fri, 2017-11-24 19:46
Tom: 1. If you want to write something in PL/SQL application server that send email automatically to an administrator requesting approval after a user create a record, which oracle package would be using. Would you also use an after insert tri...
Categories: DBA Blogs

How to define a composite primary key

Fri, 2017-11-24 19:46
I have a table with two columns that need to be defined as primary keys, order_number and item_number. A table can have only one primary key, but I can define composite primary keys in the table_constraint syntax. How do I define two columns ...
Categories: DBA Blogs

Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ?

Thu, 2017-11-23 07:06
Hello, teams:-) Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ? There has an example that I have given in Oracle 11.2.0.4.0. <code> SYS@orcl28> select round(sum(user_bytes)/(1024*1024*1024),2) fro...
Categories: DBA Blogs

Sending HTML using UTL_SMTP

Thu, 2017-11-23 07:06
Hi Tom I hope I'm phrasing this correctly... I'd like to be able to send an HTML formatted email from the database using the UTL_SMTP package. I don't see any way of setting the MIME type. Is this beyond the scope of UTL_SMTP? thanks in ...
Categories: DBA Blogs

Pages