Tom Kyte

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

Virtual Column Benefits

Tue, 2016-08-30 13:06
Hi team, I have a quick question for you today. I am creating a large (in terms of rows) table that will be queried by month, mainly for reporting purposes (each row represents a day and has a date column). Usually, when I have to design a ...
Categories: DBA Blogs

Procedure vs Package

Tue, 2016-08-30 13:06
Which is the best to user for code and when is it best to use a package rather than a procedure
Categories: DBA Blogs

12c pattern matching OOW slides

Tue, 2016-08-30 13:06
some where after OOW, i got this deck "12c_PatternMatching_CON9101_Laker-SQL - the best development language for Big Data.ppt" downloaded, but dont know if it is still available for download. Slide no 43 has an example for SQL Pattern matching exa...
Categories: DBA Blogs

Detecting deadlock

Tue, 2016-08-30 13:06
Hi, I need your help, I am new to oracle. My question is ,How can i find the deadlock in my oracle database? Your help will be appreciated,thanks in advance. Regards. DB:11.2.0.3 OS:RHEL6
Categories: DBA Blogs

Integer data type in oracle

Tue, 2016-08-30 13:06
Hi Tom, I have a default Integer assigned to the column. Can you please provide me the guidelines how can I increase the size of the Integer if data is inserting more than 38 numbers.
Categories: DBA Blogs

Analytic function

Tue, 2016-08-30 13:06
How analytic function , rowid works in deleting records from a table ? Explain with an example... Can we use goto statement in packages? What are conditions for updating a view and materialised view?
Categories: DBA Blogs

Problems in LOWER() INSIDE REPLACE()

Tue, 2016-08-30 13:06
Hi Tom, I have executed below statements. create table details (email varchar2(100)); insert into details values('a@gmail.com'); insert into details values('b@gmail.com'); insert into details values('a@gmail.com,b@gmail.com,c@hotma...
Categories: DBA Blogs

Rman backup

Tue, 2016-08-30 13:06
I have rman backup of a database. But by my mistake I purge a table which is calendars-master . So how I recover table from rman backup?
Categories: DBA Blogs

Fragmentation of table

Mon, 2016-08-29 18:46
Hi, I have a production instance which should run 24*7 I want to know How to determine and perform fragmentation of tables Please help Thank you
Categories: DBA Blogs

Split date interval into year slices

Mon, 2016-08-29 18:46
Hi, I need to split a date range into year slices like split 05-May-2015 to 05-May-2016 as start_date end_date 05_May2015 31-Dec-2015 01-Jan-2016 05-May-2016 Any help would be of great help. Thankyou
Categories: DBA Blogs

Need to Know Source of SQL Text

Mon, 2016-08-29 18:46
I have a entry in v$sql - SELECT ORG_ENTITY_ID FROM MS_QMS_FLOW_DOWN_ORG_V WHERE ACTIVITY_NAME = :B2 AND UPPER(USER_NAME) = UPPER(:B1 ). Is there a way to find out which Package/Procedure caused this Select statement to run ? PS: I am new to the f...
Categories: DBA Blogs

Performance issue

Mon, 2016-08-29 00:26
Hi Team, We have a database in there we have observed every day morning 4 to 5 AM during this one hr application user not be able to insert data into the table. We seen that in application web page where red count high means not be able to insert ...
Categories: DBA Blogs

Intermittent Ora-06502: PL/SQL: numeric or value error - For Update Skip locked

Mon, 2016-08-29 00:26
Hi Tom, We are getting Intermittent Ora-06502: PL/SQL: numeric or value error - For Update Skip locked. This error occurs only when application (.Net) calls this SP and it occurs at last pending row from table. But we are able to execute same SP f...
Categories: DBA Blogs

Gather_table_stats when UPDATING rows in a table using rownum as predicate

Mon, 2016-08-29 00:26
Hello Tom, I came across the structure where in users creating the table or updating the table uses the predicate as "rownum exec dbms_stats.gather_table_stats(xx,xx)". Can you help us to explain how this predicate structure how it works in Cre...
Categories: DBA Blogs

Multiple instances of processes resulting in deadlock

Sun, 2016-08-28 06:06
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

Sun, 2016-08-28 06:06
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

Sun, 2016-08-28 06:06
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

Pages