Tom Kyte

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

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

ora-01720 when creating a view with sys.dual

Tue, 2018-05-22 00:46
I have this create view statement that is failing with ORA-01720: grant option does not exist for 'SYS.DUAL' on line 7 create or replace view v_decisions (Sort_No, Name, Abbreviation, Include_In_EM, WPSetup_ID, Include_In_MBP, Web_Name ) as sel...
Categories: DBA Blogs

RECOVERY CATALOG AND EXPIRED ARCHIVELOG INFORMATION

Tue, 2018-05-22 00:46
Hi there... i have some doubt here.googled it many times but couldn't find anything.. i invoked RMAN and issued the folowing command.. RMAN> list expired archivelog all; using target database control file instead of recovery catalog spe...
Categories: DBA Blogs

How to check whether D2KWUTIL is deployed correctly. (in oracle forms)

Tue, 2018-05-22 00:46
Hello, We have web based forms(version : 11.1.2.2.0) running in Windows 7 environment. In the first screen (in WHEN-NEW-FORM-INSTANCE trigger), we are using D2KWUTIL's functions - DECLARE l_userprofile VARCHAR2 (2000); BEGIN --...
Categories: DBA Blogs

Confusion surrounding "buffer busy waits" and "read by other session" wait events

Fri, 2018-05-18 05:06
Tom - I'm investigating a relatively minor but attention-getting issue that occurs from time to time around my workplace. I observe a few dozen identical queries (select only - no DML) generated by an Oracle Financials concurrent program being ex...
Categories: DBA Blogs

Get the length of CLOB columns in bytes

Fri, 2018-05-18 05:06
Hello Tom, I have a table with a CLOB column: <code> create table plch_clob (i int primary key, x clob); begin for indx in 1 .. 1000 loop insert into plch_clob( i, x) values (indx, 'CLOB Row: ' || indx); end loop; ...
Categories: DBA Blogs

Data Masking

Fri, 2018-05-18 05:06
Does Oracle provide a package or function for data masking ? For example, in a development environment, for data protection purposes, the information of the table customer needs to be masked. create table customer (last_name varchar2(25), first_n...
Categories: DBA Blogs

Read only partitions in 12.2

Fri, 2018-05-18 05:06
Team, Started reading about Read Only partitions in 12.2 <u>http://docs.oracle.com/database/122/VLDBG/partition-create-tables-indexes.htm#VLDBG-GUID-9D7149B6-A2FF-47CA-8F00-47CBFD33F82B</u> <quote> A higher level setting of the read-only cl...
Categories: DBA Blogs

Generating Duplicate Rows

Tue, 2018-05-15 03:46
Hi I am new to oracle plsql and want advise on a Biz scenario: Biz want to run a shipping label report and each shipping has one record. They will choose particular shipping record and based on provided parameter they want to see number of labe...
Categories: DBA Blogs

DB Change from 12.1 to 12.2 RAC getting ORA-02297

Mon, 2018-05-14 09:26
We recently changed from a 12.1 Single Instance to a 12.2 Version running in a RAC environment with two nodes. Wa are now facing the problem that a PL/SQL procedure nearly takes factor 3 longer to run than on the single instance. Our Adminis...
Categories: DBA Blogs

Pages