Tom Kyte

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

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

Why does the year to month interval return an erroneous value compared to the day to second interval?

Mon, 2018-04-09 06:26
Hi Tom, While I was working through an example in a book, I ran into a problem I couldn't figure out. It involves two scripts and its results which are provided below: Script 1: <code> SELECT loan_id,due_date, tool_out_date, NUMTOYMINTERVAL...
Categories: DBA Blogs

Error: ORA-06533: Subscript beyond count

Mon, 2018-04-09 06:26
Please help me in editing the below Pl/SQL code: <code>create or replace Function user_score_scurve (scurve_in varchar2, inpt IN number) RETURN number IS params_number number; TYPE type_params IS table OF number; Params ...
Categories: DBA Blogs

Split 1 row into 2 rows based on column values without UNION

Sun, 2018-04-08 12:06
Hi, I will be glad if you could help me to know if the below can be achieved without using UNION I want to split a row into 2 based on a column value create table xx_test_split ( id number, amount number, discount_amount number, currency ...
Categories: DBA Blogs

Pages