DBA Blogs

Multiple instances of processes resulting in deadlock

Tom Kyte - 16 hours 43 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

Tom Kyte - 16 hours 43 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

Tom Kyte - 16 hours 43 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

Links for 2016-08-27 [del.icio.us]

Categories: DBA Blogs

To delete specific line from file using oracle

Tom Kyte - 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()

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Links for 2016-08-25 [del.icio.us]

Categories: DBA Blogs

Readers don't block Readers

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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"

Tom Kyte - 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs