Tom Kyte

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

Multiple instances of processes resulting in deadlock

14 hours 59 min ago
Hi TOMS, I have gone through the deadlocks questions posted on your website.Thanks for the answers you have shared. However I have a scenario where there are multiple instances of a process are running and causing a deadlock. 1. Design of proc...
Categories: DBA Blogs

How to Insert Random PKs from Child Tables Into Parent Table's FKs

14 hours 59 min ago
How can I randomly generate and insert an "x" number of rows into a parent table (x being independent of the size of the parent and children tables), where it (the parent table) might have multiple FK columns from two or more child tables? For ins...
Categories: DBA Blogs

SQL questions involving combinations

14 hours 59 min ago
Dear Tom... I have two questions that are somewhat related to each other in concept: 1) Consider the following table which represents a state history per each person identified by PERSON_ID: SQL> CREATE TABLE status_history 2 (per...
Categories: DBA Blogs

To delete specific line from file using oracle

Sat, 2016-08-27 11:46
Hi Tom, is there any way to delete the specific line from the file using oracle? Actually i have around 500 files, and i need to perform the same action on the all files. Is there any process?
Categories: DBA Blogs

Table join vs Rank()Over()

Sat, 2016-08-27 11:46
Hi!, recently I came with the task of getting the newest address from customers from an ORACLE JD Edwards database. In JDE, the table F0101 is the Address Book Master and table F0116 is Address by Date. I got two solutions: one using joins: SELE...
Categories: DBA Blogs

Index rebuild

Sat, 2016-08-27 11:46
Hi team, As i want to know that the view in oracle with the help of that we can check when the index is required to rebuild. like leaf node , deleted node something So, could you pls let me know the exact view or query with the help of that we can...
Categories: DBA Blogs

storing and indexing prefixed values

Sat, 2016-08-27 11:46
Hi Tom, We've got a table that'll hold about 1M rows over the course of its lifetime. We need to add a column that the business want to search for, so indexing it seems like a good idea. Column is - a 10-character string, - mostly unique (...
Categories: DBA Blogs

Difference between DBMS and UTL packages

Sat, 2016-08-27 11:46
Hello, There are around 13 UTL packages for accomplishing many purposes in Oracle database, and their package name starts as UTL_(some text). For eg., UTL_SMTP, UTL_TCP, etc. I was just wondering why we have the name of these packages as UTL_(s...
Categories: DBA Blogs

Isolation Level Serialization

Fri, 2016-08-26 17:26
Hi, We have two sessions: Setup Data: insert into table1 values(table1_seq.nextval, 'A', 'AA'); insert into table1 values(table1_seq.nextval, 'B', 'BB'); commit; Session 1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; select * from ta...
Categories: DBA Blogs

Conditionally join (left or inner) two table

Fri, 2016-08-26 17:26
Hi! First of all thanks for taking time to read and answer my question. So, I got two set of rows which needs to be joined. In some cases it should be outer left join, but in rest it should be inner. Is it possible make such query? Small ...
Categories: DBA Blogs

Commit Frequency and Rollback

Fri, 2016-08-26 17:26
Hi Tom, In our application, we have set the commit frequency as 50 and in one case one record fails to get inserted (due to a constraint violation exception). So, in that case, would all the 50 records in that commit slot get rolled back? P...
Categories: DBA Blogs

SQL Translator in 12c

Fri, 2016-08-26 17:26
Started reading about 12c SQL Translator from documentation <u>http://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#CHDEIGFA</u> Here is a quick demo on that. <code>demo@ORA12C> create table t1 as select * from all_objects; Table cre...
Categories: DBA Blogs

pivot twice

Fri, 2016-08-26 17:26
First, the following sql return this. select trim(p.proc_name) as com_client ,op.name as trait_name , op.value trait_value from oen_procinfo p , oen_personality op where op.interfaceid = p.interfaceid and not p.proc_name like ...
Categories: DBA Blogs

Bulk Collect

Fri, 2016-08-26 17:26
There is column called jobstream_dt in a table spc_records I want to run a Delete script using Bulk collect technique to delete rows from that table where jobstream_dt is older than 540 days. We have around 125 million rows in that table which are...
Categories: DBA Blogs

Readers don't block Readers

Thu, 2016-08-25 23:06
HI Connor Readers don't block Readers, is this true? As I understand waiting for a resource is also a kind of blocking. I am waiting because the access is blocked. Then please help understanding the below " ---- Another example of a prob...
Categories: DBA Blogs

Oracle forms to create a database user

Thu, 2016-08-25 23:06
Hello, Is there a way to create a form with username and password and create the user in the database? I have tried using a host command to log into sqlplus and execute the create user .... but it doesn't want to execute from the form host comm...
Categories: DBA Blogs

WHILE Loop Running forever

Thu, 2016-08-25 23:06
Hi , My aim is to print week days from a given date, for ex : IF I provide a date like '12-DEC-2012' ( Which is Thursday ) then my program should print as below FRIDAY SATURDAY But below code is running in a loop for ever, I am unable ...
Categories: DBA Blogs

Lock for "grant"

Thu, 2016-08-25 23:06
Hi Tom, When we *grant* a privilege on a table acquires a lock on the table. Just curious to know the following... -- Why does Oracle acquires a lock on the table (apart from grant being a DDL) when we issue a grant. Does it change the definiti...
Categories: DBA Blogs

Synchronous refresh cannot be used for the non-partitioned tables?

Thu, 2016-08-25 23:06
Hi, as I want to use the new technology in 12c called "Synchronous refresh" of the MView or group of MViews, I read the documentation ( https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG029 ), but for me it's not 100% clear what are the res...
Categories: DBA Blogs

The last query gives the error "Invalid Identifier error".Please explain me whether I have refer the correct table?

Thu, 2016-08-25 23:06
---------------------types------------------- create type course_ty1 as object( cid number, titlte varchar(15), credits_req integer ) / create type unit_t as object( ui number(5), credit number(5) ) / create type offering_t as obj...
Categories: DBA Blogs

Pages