Tom Kyte

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

Fastest Delete in 2TB table

5 hours 15 min ago
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_...
Categories: DBA Blogs

Get the details of *historical* queries/sessions/sql_id generating huge redo for last one week.

5 hours 15 min ago
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...
Categories: DBA Blogs

Dynamic sql without a known result set

5 hours 15 min ago
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...
Categories: DBA Blogs

Spool the file including headers

5 hours 15 min ago
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 ...
Categories: DBA Blogs

Supplemental logging levels

5 hours 15 min ago
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...
Categories: DBA Blogs

Reading deadlock trace files

5 hours 15 min ago
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...
Categories: DBA Blogs

Alternative for GROUP_CONCAT() and FIND_IN_SET() in mysql for selecting values from a column with comma separated values.

5 hours 15 min ago
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...
Categories: DBA Blogs

processes parameter in oracle

5 hours 15 min ago
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 ...
Categories: DBA Blogs

Performance issues

Tue, 2017-08-22 18:46
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...
Categories: DBA Blogs

User-Defined Aggregate function with more than 1 argument

Tue, 2017-08-22 18:46
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...
Categories: DBA Blogs

Unable to set pctversion to 0

Tue, 2017-08-22 18:46
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_...
Categories: DBA Blogs

Performance of querying CHAR columns

Tue, 2017-08-22 18:46
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...
Categories: DBA Blogs

Remove redundant entries from table

Tue, 2017-08-22 18:46
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'...
Categories: DBA Blogs

Load XML File (Physical file) using SQL Loader into a XMLType Column

Tue, 2017-08-22 18:46
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...
Categories: DBA Blogs

ORA-00972: identifier is too long

Tue, 2017-08-22 18:46
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...
Categories: DBA Blogs

DATE output format

Tue, 2017-08-22 00:26
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...
Categories: DBA Blogs

Database Upgrade / APEX versions

Tue, 2017-08-22 00:26
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...
Categories: DBA Blogs

Inserts into materialized view tables are parsed each time.

Tue, 2017-08-22 00:26
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...
Categories: DBA Blogs

Search for top 10 queries generating huge redo

Tue, 2017-08-22 00:26
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...
Categories: DBA Blogs

use clause optionally enclosed by '"' in sql loader 11.2.0.2.0

Tue, 2017-08-22 00:26
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...
Categories: DBA Blogs

Pages