Tom Kyte

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...

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 ...

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...

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...

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...

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 ...

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 ...

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...

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...

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) ...

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...

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 ---------- -----------------...

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) := '...

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...

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 ...

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....

Hi, I have a Production database ( version shown below) with about 2000 tables, having over 10 million rows each. <code>BANNER ----------------------------------------...

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...

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...

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 --...