Tom Kyte

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

Avoid full-table scan when filtered by ora_rowscn

Fri, 2018-04-13 02:06
Hello, We have a need to extract data using "sqoop" on a bunch of tables. However, there is no column defined in those tables that store information like "update date or last modified date". We are thinking of using ora_rowscn as a filter. Howe...
Categories: DBA Blogs

Column deleted by mistake

Fri, 2018-04-13 02:06
Hi Tom, Thanks for wonderful support always. I want to know is there any way to know who has deletes the entire column from the table. There is no manually created trigger in my database for auditing on column delete. One entire column ha...
Categories: DBA Blogs

Conditional execution one of two parts of union all query

Fri, 2018-04-13 02:06
I have have two complex, expensive queries that I have performed a union all to bring together for use in a report tool I have a bind variable that, if set to one value, the union query should execute the first part of the union. If the bind ...
Categories: DBA Blogs

In order to connect to 12C DB, do we have to change our client version to 11.2.0.3(or above)?

Fri, 2018-04-13 02:06
Hi Team, We've installed a latest Oracle Database 12C Release 2 on OEL6.8. And found that we could not connect to our DB unless using client which version is above 11.2.0.3(included)! Here's our senario: First, we tried to connect to DB using 11...
Categories: DBA Blogs

Data index inside compressed index for cardinality calculation?

Thu, 2018-04-12 07:46
I have been wondering why Oracle 11 does not follow explained plan for subquery execution with cost based optimiser. It selects other index for execution which causes very slow performance 34M row table. Table got 900 000 rows in seven weeks. Pro...
Categories: DBA Blogs

Interval partition on index organized table

Wed, 2018-04-11 13:26
Hi, Some points related to my query, 1. We are going to do partition for table, have almost 1.7 TB in size and have 6 Indexes. 2. We are planing to Use Exchange Partition method. 3. In that table have "Operation_date" column which we want to...
Categories: DBA Blogs

function that will return the text between <a> and </a>

Wed, 2018-04-11 13:26
Hi tom, Suppose i have a text like <a>hkjfsdfjashkdfhask_75274_jhsdfbajh</a> Now i need a pl/sql function in which i will send <a> and </a> as parameters . Then the function will return me the text of above hkjfsdfjashkdfhask_75274_jhsdfbajh . ...
Categories: DBA Blogs

ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded when XMLROOT is used

Wed, 2018-04-11 13:26
We are hitting below error when we try to insert version and encoding to xmltype variable, using XMLROOT in our plsql code: <code>ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded DIAGNOSTIC ANALYSIS: --------------...
Categories: DBA Blogs

Question on num_rows in dba_indexes

Wed, 2018-04-11 13:26
Hi - I just ran a full schema stats on one of our schemas. After the full schema stats was complete, I checked the num_rows in dba_tables and num_rows in dba_indexes. The num_rows from dba_tables looks good but num_rows from dba_indexes shows as 0 fo...
Categories: DBA Blogs

How to return multiple columns in case statement

Tue, 2018-04-10 19:06
I have query like: <code>select case when 1 in (1,2,3) then (select 'abc' as "name 1",'xyz' as "name 2" from dual) else 'pqr' end from dual; </code> How can I return two columns if a condition is satisfied in case?
Categories: DBA Blogs

Exchange Partition and Nested Tables causing ORA-14132: table cannot be used in EXCHANGE

Tue, 2018-04-10 19:06
Hi, I am using EXCHANGE PARTITION command on my database, so the live table has the least unavailable time. I have a partitioned table, with approximately 20 columns; 5 of them are nested tables. I also have a non-partitioned table which has e...
Categories: DBA Blogs

insert in blob with dblink

Tue, 2018-04-10 19:06
Tom, Thanks for your time. I have two dbs running 11.2.0. I create a DB link named dblk_b from DB_a to DB_b. I have a table by name 'tableB' like below in database DB_b. ID NUMBER(11), CONTENT CLOB I am trying to insert into tableB as fol...
Categories: DBA Blogs

get 3 consecutive dates (based on sys date) in a single column and map it with another Table's column

Tue, 2018-04-10 00:46
Hi, I need to show Quantity sold for three consecutive days (yesterday, today, tomorrow). So, date should be the first column and Quantity being second. Desired Output -------------- Date Qty .... ... 8/4/2018 10 9/4/2018...
Categories: DBA Blogs

Initializing a PLSQL table of records

Tue, 2018-04-10 00:46
Tom, How do you initialize a PL/SQL table of records in the Declaration section of a PL/SQL block? In the following snippet, I can successfully initialize a normal scalar PL/SQL table but am unsuccessful initializing a table of records. Can it...
Categories: DBA Blogs

Oracle Reports 11g showing junk characters fron nvarchar2 column stored in Oracle database 12c

Tue, 2018-04-10 00:46
Dear Sir, Our Oracle 12c database and Weblogic 11g are hosted in SunOS 5.11 11.2 environment. The output from nls_database_parameters is given below: NLS_RDBMS_VERSION 12.1.0.2.0 NLS_NCHAR_CONV_EXCP ...
Categories: DBA Blogs

Export all table in a schema into csv files

Tue, 2018-04-10 00:46
Hello, I want to export all table in a schema into csv files. I want that the csv files having the same name as the tables. I have following SQLPlus Code: <code>connect username/password set serveroutput on; set lines 80 set head off...
Categories: DBA Blogs

Problem with large tables and LIKE query

Tue, 2018-04-10 00:46
Hi, we have an application that uses Oracle database to hold company data. One of it's table, called PWORKSPACEOBJECT holds all 'displayable' objects in GUI client. Most of the time users are searching for some data only by typing *some text* ...
Categories: DBA Blogs

ORACLE SQL Question sum credits and debits

Mon, 2018-04-09 06:26
I have a transaction table called TRANSACTION DETAIL.... TRANSACTION DETAIL CNN TYPE AMT DATE C1 C 1000 10-Jan-16 C2 C 1200 10-Jan-16 C3 C 2000 11-Jan-16 C4 D 1000 12-Jan-16 C3 D...
Categories: DBA Blogs

Unable to get expdp/impdp utility run successfully, getting ORA-39002: invalid operation ORA-39070: Unable to open the log file.

Mon, 2018-04-09 06:26
Dear Tom, very Good day to you. I am trying to use expdp/impdp utility to backup tables,schema etc and it is not executing successfully. The followings are what I am getting in regard to errors <code>ORA-39002: invalid operation ORA-39070: Un...
Categories: DBA Blogs

Where is the web address of the "Partition" introduction details on AskTom ?

Mon, 2018-04-09 06:26
Hello, AskTom Team. A few months ago, I have glanced a link about "<b><i>Partition</i></b>" introduction details. But now I haven't seen it. Please tell me what web address is. Best Regards Quanwen Zhao
Categories: DBA Blogs

Pages