Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 hour 56 min ago

Regular Expression is not working if the search criteria with LIKE and NOT LIKE in single input field

1 hour 56 min ago
Hi Tom, Need your help ! Please find the LiveSQL link Thanks in Advance ! I have a table st_exp with s_desc column only, user has option to search by s_desc criteria. Scenario :The user may enter text critiria LIKE and NOT LIKE in the ...
Categories: DBA Blogs

Error in date comparison for partitioned table

Fri, 2018-05-25 02:06
Hi Tom, I am seeing a strange issue with a query which queries data from a partitioned table having sub-partitions. Please see table, query and the error. Can you please help, what could be the reason for this error? <code>CREATE TABLE trans_de...
Categories: DBA Blogs

Procedure Performance Number vs Pls_integer

Fri, 2018-05-25 02:06
I have task to improve performance in some of the packages and procedures in our application. We have 1 package and it has subprograms around 15-20 procedures. Below are my clarifications required. Iam making changes to datatypes from NUMBER to...
Categories: DBA Blogs

XMLQuery

Thu, 2018-05-24 07:46
Hi Tom, I am trying to learn XQuery use with SQL but it looks very complicated. Can you please advise with some simple cases (I am not interested in XML generation but using XML and XQuery functions with relational data tables). Where to start? Wh...
Categories: DBA Blogs

Oracle Database - Grant/Revoke High Concurrency

Thu, 2018-05-24 07:46
We have an Oracle 10g release 2 database running on a production environment. It's experiencing a lot of concurrency, as Sql Developer 17.4 "Waits for past 1 hour" graph shows. When the database is behaving slow, we take a look at that graph, and ...
Categories: DBA Blogs

Can I user automatic List in subpartitions?

Thu, 2018-05-24 07:46
Dears, I have a table that contains two columns one for year and the other for month. I need to partition this table based on year and month, where year represent the partitions and under that the month represent that sub-partitions. What I need i...
Categories: DBA Blogs

Multiple block allocation to small table

Thu, 2018-05-24 07:46
Hi, I executed below query on my database and found given output: <code>select a.table_name, a.NUM_ROWS, a.AVG_ROW_LEN, a.LAST_ANALYZED, a.SAMPLE_SIZE, a.blocks from user_tables a where num_rows <10;</code> Output: <code>TABLE_NA...
Categories: DBA Blogs

Nested loop and hash join.

Thu, 2018-05-24 07:46
Hi Tom, Can you help me in understanding how optimizer decides which join ( hash or nested loop) it will use for joining. Also which is the driving table in nested loop. There are lot of confusing answers on this on internet, which one to rely...
Categories: DBA Blogs

Create a physical standby for 12c RAC

Thu, 2018-05-24 07:46
hi - this weekend we have a project that is going live. we will be importing data (about 1TB) into the database. after that we want to create the physical standby. what is the best, efficient and most proven way to create a physical standby database ...
Categories: DBA Blogs

How to gather statistics on a standard edition database

Thu, 2018-05-24 07:46
Hi, I'll like to gather some statistics on long running statements on a standard edition database. Can you please suggest the best way to gather stats on this statement? <code> BANNER ...
Categories: DBA Blogs

Limit and conversion very long IN list : WHERE x IN ( ,,, ...)

Thu, 2018-05-24 07:46
How many elements may be in the WHERE x IN (,,,) list ? I see 2 ways to overcome IN list limitation: 1) use x=el_1 OR x=el_2 OR x=el_3 OR ... 2) create temporary table , but another question arise here: why create table A( X INTEGER, Y...
Categories: DBA Blogs

ORA-01659 on creation of a not unique global partitioned index

Tue, 2018-05-22 19:06
Dear Tom, I have a table that stores climatic data with this layer: idcell,day,field1,.... This table is locally partitioned by range on day and it has a local PK index: idcell,day. I want to create a not unique global partitioned index on i...
Categories: DBA Blogs

PL/SQL programming to write to file in batches of 2 million rows

Tue, 2018-05-22 19:06
I have been assigned to the task. Task :- In one table , I have 10 million records and I need to export table data into a CSV files/Text files but the condition is that(I need to export into 5 files ,each file should contain 2 million records) 1) ...
Categories: DBA Blogs

Partition pruning with MEMBER OF operator

Tue, 2018-05-22 19:06
Hello Tom ! Is it possible to force Oracle to use (sub-)partition pruning when MEMBER OF operator is used on some nested table? For example: <code>SELECT * FROM A_TABLE WHERE COL_1 MEMBER OF NUMBER_TAB_TYPE(1,10,4);</code> where NUMBER_TAB_TY...
Categories: DBA Blogs

HOW TO GET OLD VALUE OF A DATA

Tue, 2018-05-22 19:06
HI,THERE I have a situation here, in one of my table, i hvae loc_id column my requirement is that i want all loc_id that have changed to new loc_id eg: LOC_ID CUST_NAME ---------- -----------------...
Categories: DBA Blogs

How to change the subscript/index value in an associative array?

Tue, 2018-05-22 19:06
Hi Tom, Is it possible to change the subscript/index value of an existing element in an associative array? <code> declare type a_arr is table of varchar2(20) index by pls_integer; tb1 a_arr; begin tb1(1) := 'aaaa'; tb1(2) := '...
Categories: DBA Blogs

TDE Encryption Wallet Change Password

Tue, 2018-05-22 19:06
Requirement We need to be able to change the password on our Oracle TDE Encryption Wallet, which we use to encrypt some of our Tablespaces. This frequently asked questions article states that this can be done with OWM or orapki http://www.orac...
Categories: DBA Blogs

Costs associated in a query execution plan

Tue, 2018-05-22 00:46
Hello, I have a question to be clarified related to costs associated with SQL query execution. I have view built on 3 big tables. <code>CREATE OR REPLACE VIEW TEXT_TRANSLATION AS SELECT c.module ...
Categories: DBA Blogs

Write PLSQL procedure to get query result in an excel automatically

Tue, 2018-05-22 00:46
Hi Though I have checked in google many times for the answer of my question 'Write PLSQL procedure to get query result in an excel automatically', till I am not having clear understanding . Could you Please explain it in simplest way possible....
Categories: DBA Blogs

Update all database tables with current timestamp

Tue, 2018-05-22 00:46
Hi, I have a Production database ( version shown below) with about 2000 tables, having over 10 million rows each. <code>BANNER ----------------------------------------...
Categories: DBA Blogs

Pages