Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 day 3 hours ago

Truncate Partition is very slow

Tue, 2020-02-11 18:04
Hi Tom We got a table which is partitioned on Range (date), Each partition holds approximately 5 Million records, there are 60 partitions. There are 4 Indexes on this table and they are Global Indexes. To remove old data we truncate partition. ...
Categories: DBA Blogs

Scheduling Queries

Tue, 2020-02-11 18:04
Hi Connor, Chris, Could you please have a look at below scenario related to dbms_scheduler program and job setu: <code> -------------- Start Use Case Setup -------------- -----------------------------------------------------------------------...
Categories: DBA Blogs

Labs for Multitenant

Tue, 2020-02-11 18:04
Hi All: I have a question on the Multitenant Fundamentals hands on session scheduled for Thu, Feb 13, 2020 16:00-17:00 UTC: Will these labs be making use of VMs based on https://cloudmarketplace.oracle.com/marketplace/listing/69658839? * I'm a...
Categories: DBA Blogs

Oracle Sequence expiry

Tue, 2020-02-11 18:04
I have few sequences which are about to expires and I need notification over emails when last_number increases. Can you suggest how to do it.
Categories: DBA Blogs

ORA-30926 not raised in merge statement for non-deterministic set of rows

Tue, 2020-02-11 18:04
Hi Connor, Chris et al, Could you please kindly help me better understand when ORA-30926 is to be raised for non-deterministic set of rows on input? Up to 12.1 it worked as a charm , regardless of sort order and number duplicate rows on input O...
Categories: DBA Blogs

ORA-06553: PLS-306: wrong number or types of arguments when selecting object type instance

Tue, 2020-02-11 18:04
We're experiencing this when testing upgrade from Ora 12.1 to 19. What's wrong with the code?
Categories: DBA Blogs

Unable to find or delete BIN$ Tables

Tue, 2020-02-11 18:04
While I was examining the space used on my free ADW i found lots of tables with a prefix of BIN$ in my DATA tablespace. I found these being referenced in the user_segments The recyclebin is empty, and I cant find the tables using a select on the u...
Categories: DBA Blogs

Long Running PROC takes over 7 hours

Tue, 2020-02-11 18:04
Hi Tom, I have a PROC that will be run infrequently. It will be used to delete rules from a table. There are 75,000 rules and over 14,000 will be deleted in the first run of the proc with another 1200 potentially deleted. I need to check if ...
Categories: DBA Blogs

Node switchover data loss

Tue, 2020-02-11 18:04
Hi , We faced one issue, I have explained the scenario > Our table has multiple rows with primary key as number for an entity For ex: assuming employeeid for an employee > We have a status column which suggests calculation status of the current...
Categories: DBA Blogs

how to load XML file into oracle table and using XML query to get result

Fri, 2020-02-07 18:03
Hi Tom, I have XML file from Web Report. there are some error records in the file, it is difficult to find the error in XML file, I want to load this file into oracle table. then use XML query. below is sample file and expected result. This is fir...
Categories: DBA Blogs

ORA-29279: SMTP permanent error: 550 XXXsmtpXX: Host xxx.xxx.xxx.xxx: No unauthenticated relaying permitted

Fri, 2020-02-07 18:03
Hi Tom, I want to send email through PL/SQL by using utl_mail package, I have completed below steps 1. Installed scripts <b>@$ORACLE_HOME/rdbms/admin/utlmail.sql @$ORACLE_HOME/rdbms/admin/prvtmail.plb</b> 2. granted execute to us...
Categories: DBA Blogs

Select returns rows that it should not

Tue, 2020-02-04 09:02
Hi. I have a reproduction of strange behavior of select statement. Reproduction: <code> --drop table test_a1 --drop table test_a2 create table test_a1 ( id1 number(19), value1 number(1) ) / create table test_a2 ( id2 number(19), v...
Categories: DBA Blogs

Creating index for interval values

Tue, 2020-02-04 09:02
Hi! Is it possible to create an index with some kind of "between" option? I have a dimension-table with 30 columns where the unique key is a combination of 2 columns (from_minute, to_minute). Primary key is a unique sequence. Total number of ...
Categories: DBA Blogs

Archiving Using DBMS_DATAPUMP

Tue, 2020-02-04 09:02
I have a requirement to archive historical data, that is delete data from a number of tables that is more than 36 months old, but only a subset of data, for arguments sake lets call them quotes given to customers that have not been taken up. We curre...
Categories: DBA Blogs

How many Not guaranteed Restore Points can I use at given point

Tue, 2020-02-04 09:02
Hello! We have a development database where we won't do any backup of the database. Our method to recreate it will be rman duplicate from the production one. But we want to give the option to our development team to do a flashback of that database...
Categories: DBA Blogs

How to tune a create table that runs in loop

Tue, 2020-02-04 09:02
Hi All, can someone help me to tune this peice of code. It is taking 45 minutes to complete the execution as of now. We have tried taking off the loop and creating 5 different create statements but did gain just 2 minutes Each of the table have and...
Categories: DBA Blogs

Truncate Multiple tables in a single line statement.

Tue, 2020-02-04 09:02
My question: Is it possible to truncate multiple tables in a single line statement? Firstly, thanks for giving this opportunity to ask a question. I have developed a code to truncate multiple tables in a single line statement. Please refer the ...
Categories: DBA Blogs

Getting error -12842 : ORA-12842: CURSOR INVALIDATED DURING PARALLEL EXECUTION

Tue, 2020-02-04 09:02
Below is my scenario. We are getting error randomly in production. when we restart the process error disappears. we are not able to reproduce even in lower environment. Can you please help in letting us know the potential issue with insert statement ...
Categories: DBA Blogs

How to retrieve the 200millions of record into txt file using select query?

Tue, 2020-02-04 09:02
Hi Tom, We have 200+millions of records in our table, we have to retrieve all the records into text file without impacting the performance. I tried using DBMS_parallel_execute but this doesn?t have return type. Is there a way to get all Millions of...
Categories: DBA Blogs

SQL query join by GUID

Tue, 2020-02-04 00:02
Hello, Ask Tom Team. I have to create a report to show some business data. The data to show is stored in two different Oracle databases. I have to write a SQL query joining two the databases (dblink). The two databases were designed for very diffe...
Categories: DBA Blogs

Pages