Tom Kyte

Hi, I have a 2TB table. It has also large index. I want to delete records from this large table with date key. Date key is a column in side table. Below is my query. But it takes huge time. delete /*+ PARALLEL(6)*/ FROM F_CUST_TRANSACTION_...

Dear Experts, To my recent past query from the below link was very helpful. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536412000346656749 Can you please help me on how to get the details of *historical* queries/sessi...

I have a porject that am working on which involves multiple tables with millions of rows each, I plan to use the dbms_parallel_execute to process the table in chunks The situation is as follows Tablea has columns col1 that needs to be uypdated t...

Hi, While spooling the fie how can I display which objects got spooled. for example, in my spooled file I want to display as below, spool file /u01/oracle/spool_test.sql # table creation script for emp_table table created. how can ...

Hi Ask Tom team, We are building a CDC from our Oracle OLTP systems, using Golden Gate or related technology. The developers, including myself, wanted to enable supplemental logging on all columns for us to build streaming analytics solutions, whi...

Hi Tom ! How can I understand which two rows where involved in the following deadlock, which appears in this excerpt of a tracefile ? Especially in the last two lines, Oracle seems to convey this information, but I was not able to locate the o...

Hi Team, I wanted to share a LiveSQL link but unfortunately I created the test case and somehow I lost the link. Here is my test case in documented format. - I have two tables - create table employee(empid number, empname varchar2(20), de...

Dear Tom, I have load testing on DR database now i have 4000 processes (show parameter processes), now i want to increase it on DR site and DR Database in Snapshot standby mode only. Doubts- 1. Can you explain what is the use of processes in ...

I'm getting performance issues while running the below query. Its taking 7 minutes to give results Table - V_DM_test_VISIT contains 25 million records table - V_DM_PLANNED_VISIT contains 60 K records I'm using analytical function to find o...

Is it possible to create a user-defined aggregate function with more than 1 argument? Let say, I would like to create TOP_NTH(number, integer) function that takes a number (for a column) and integer indicating the ranking of the top value. Such funct...

Hello Tom, I am trying below code to reset table pctversion to 0 from default 10. but on Oracle 12c 12.1.0.2 it won't work, but the same command works on Oracle 12c 12.2.0.1 and also works on 11gR2. <code> alter table ev_log modify lob (event_...

I wasn't able to parameterize our queries because our database schema uses a lot of CHAR types on key fields, but was told to use the LIKE operator instead of =. This did allow the parameterized queries to work, but I'm concerned how this will effect...

Hi Experts, Please find below table & data. <code>create table orders ( order_id varchar2(10),quantity varchar2(10), price varchar2(10), id varchar2(10)); insert into orders values ('O1','2','100','id1'); insert into orders values ('O1','3'...

Hello Tom I need to load a XML File, which is available on the oracle server using a Host Program in Oracle Applications. I have done the below 1. Created a Host Concurrent Program 2. Call the SQL Loader Control File from the PROG File. But...

I have a question about "Identifier too long" error. I understand if I am trying to create a column name that is too long and oracle complains on it. but if I do a select and alais it my own text, why is this a problem ? Here is a sample: <co...

Hi, In database_properties table date format is in 'DD-MON-RR', but in table it showing different format in date columns. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Ad...

Hi Team, We're planning to upgrade our database from 11.2.0.4 to 12.1.0.2 (non-pluggable configuration). The current version of APEX installed in our environment is 5.1 My question is - during the upgrade process, is the installer smart enou...

Hi We have a PL/SQL application which is inserting into database tables. These tables have materialized view logs. Noticed when looking into v$sql, that number of executions of these insert statements is much greater then number of parsing ope...

Dear Experts, Please can you help in knowing how to find for top 10 queries/sessions generating huge redo. I tried with the below queries to check redo generated per day, but not able to find top 10 such sessions which generates so. select t...

I have strange behavior using optionally enclosed by '"' in my .ctl file. it doesn't works as expected and I do understand why. Could you please help me? here the .ctl file: <code>OPTIONS (PARALLEL=TRUE,DIRECT=TRUE,ROWS= '150000',BINDSIZE= '4500...