Tom Kyte

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

HASH JOIN Vs SORT MERGE

Mon, 2016-08-01 12:46
Hello Asktom Team, Hope you could throw some light on this. We have two Oracle 11g instances where one MERGE statement has radically different execution plans. One finishes in less than a minute and one times out with ORA-01652: unable to exten...
Categories: DBA Blogs

Distribution of DOP

Mon, 2016-08-01 12:46
I'm using Oracle 12c. I'm calling mutiple procedures from Python in parallel (usually 4-5). I make sure nothing else is running at that time. All the target and source tables has Parallel 32 Clause . I'm using hints ENABLE_PARALLEL_DML, PARALLEL in...
Categories: DBA Blogs

Count groups when next value = current value plus one

Mon, 2016-08-01 12:46
I have this table: create table test (myname varchar2(1), dts_id number); With this data: insert into test (myname, dts_id) values ('A',1); insert into test (myname, dts_id) values ('A',2); insert into test (myname, dts_id) values ('A',3); in...
Categories: DBA Blogs

Pivot table row to column covert

Mon, 2016-08-01 12:46
Hi Tom, Please consider below scenario. Table A ID Attribute Value 1 'X' 3 1 'Y' 4 2 'X' 5 3 'X' 6 3 'Y' 7 3 'Z' 8 3 'A' 9 Expected output ID X Y Z A 1 3 4 nul...
Categories: DBA Blogs

Please give the sql to get query performance in oracle database, i don't want to use statspack.

Mon, 2016-08-01 12:46
I was SQL Server DBA over 1 year, now i am oracle DBA so i used to use below query against sql server database to get query performance. <code>SELECT TOP 5 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.obje...
Categories: DBA Blogs

DBMS_DATAPUMP

Mon, 2016-08-01 12:46
Can one export and import data only using dbms_datapump? I do not want to specify a schema on the export nor do I want it to default to the user's schema. I do not want to specify a "from" schema on the import. Is this possible?
Categories: DBA Blogs

email attachment but not enable to open it

Mon, 2016-08-01 12:46
I am trying to send mail with attachment pdf file i recieved the mail but when am trying to open the pdf file is return error massege please help am stuck DECLARE v_From VARCHAR2(80) :='test@ttt.net'; v_Recipient VARCHAR2(80) := 'r...
Categories: DBA Blogs

IO throughput problem

Mon, 2016-08-01 12:46
Dear Sirs, My question is about throughput\bandwith problem. We have a data warehouse solution, database size aroud 1 TB, nightly\morning run times around 15 hours. The optimal performance would be that users can have their daily generated repo...
Categories: DBA Blogs

Real Time Scenarios & duplicate rows

Fri, 2016-07-29 11:26
I have two questions 1.) Deleting Duplicate rows DELETE FROM EMP1 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP1 GROUP BY EMPNO); the above command deletes the duplicate rows from a given table such as emp1 in this case, group by eliminat...
Categories: DBA Blogs

Cascading calculated LAG values

Thu, 2016-07-28 17:06
Oracle 11.2.0.4 Example SQL to explain my question: https://livesql.oracle.com/apex/livesql/file/content_DMZ4GO3W9QL4BONQQB8I7NP5X.html The SQL I am working on is monthly data based on two categories (CATEGORY_A and CATEGORY_B). While we get valu...
Categories: DBA Blogs

DDL timeout parameter limit

Thu, 2016-07-28 17:06
Hi Tom I have read in the below link that DDL will not require any downtime starting from Oracle 11g (understood that same applies to Oracle 12c): http://www.oracle.com/technetwork/database/features/online-ops-087977.html I am using Oracle 12c...
Categories: DBA Blogs

Return first character of each word

Thu, 2016-07-28 17:06
Sir can you help me regarding this query in SQL by using character functions only.... I/P : ABC DEF GHI Output: ADG
Categories: DBA Blogs

ORA-00980 translation is no longer valid

Thu, 2016-07-28 17:06
Dear Sir, I am exporting a database from one system and importing it in another system. after importing its shows the error ORA-00980 tranlation is no longer valid everywhere i can see the solution but my question is 1)its working well with wit...
Categories: DBA Blogs

Invalid rowid in deadlock trace file...I must be missing something

Thu, 2016-07-28 17:06
Hi! We recently had an event where a trigger was changed by only a few lines resulting in hundreds of deadlocks a minute. We changed it back and all is well. I'm trying to figure out what happened though. The change in the trigger was if inserting s...
Categories: DBA Blogs

defferable

Thu, 2016-07-28 17:06
Hi Sir i am new for oracle. ____Q1) 2. Disabled validate - 3. Enabled novalidate 4. Enabled validate Please explain briefly with primary key constraints only with example (either 3Q or 4Q take any one ) Q2)while doing outer join(left/reght) ...
Categories: DBA Blogs

Code enchancement

Thu, 2016-07-28 17:06
Hell Chris, i have tables called t1 and t2. Table T1 Script as follows: <code> DROP TABLE T1 CASCADE CONSTRAINTS; CREATE TABLE T1 ( ID NUMBER ) Insert into T1(ID) Values(2001); Insert into T1(ID) Values(3001); Insert into...
Categories: DBA Blogs

CASE COLUMN WHEN NULL

Thu, 2016-07-28 17:06
hi Why doesn't NULL_CASE2 return the same result as NULL_CASE1? CREATE TABLE CASENULL (DUMMY VARCHAR(10)) INSERT INTO CASENULL VALUES (NULL); INSERT INTO CASENULL VALUES ('X'); COMMIT; SELECT NVL(DUMMY,'NULL') AS DUMMY, CASE WHE...
Categories: DBA Blogs

Exact count of alphabet

Wed, 2016-07-27 22:46
Sir, I want the query so that I have the names of person who are having exactly have 2 'a' in his or her name.. M trying query but m getting more then 2 'a'. But i want exactly two. Thanks.
Categories: DBA Blogs

data pump api - keep master

Wed, 2016-07-27 22:46
Is there a way to keep the master table on the successful completion of a data pump import/export using the API? There looks to be a way to keep the master table when using interactive data pump (keep_master=Y).
Categories: DBA Blogs

database migration from sun sparc to solaris x86_64

Wed, 2016-07-27 22:46
We are planning to migrate our databases from sun sparc machine to solaris x86_64. Both are different endian format. Could you please let me know the best approach to migrate these databases
Categories: DBA Blogs

Pages