Tom Kyte

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

Number of Execution per snapshot too high for rman sql

Tue, 2016-08-09 04:06
Hi Team, I have been seeing many executions of below query in our database. begin sys.dbms_backup_restore.createRmanOutputRow( l0row_id => :l0row_id, l0row_stamp => :l0row_stamp, row_id => :row_id, row_stamp =>:row_stamp, txt=> :txt, sameline ...
Categories: DBA Blogs

Trigger to allow insertion only on Sunday

Tue, 2016-08-09 04:06
I have employee table. I want to create a trigger that will not allow insertion in the table on sunday.</b> Tell me the program please.. Thank you.
Categories: DBA Blogs

JSON from Relational Data

Tue, 2016-08-09 04:06
With all the new JSON Features, is there a way to take queries over relational data (ie. normal Oracle tables) and on the fly generate JSON objects? More and more vendors are using REST based apis that process JSON (a key one for us is Oracle Sales ...
Categories: DBA Blogs

Performance imact of adding 200+ columns to a table

Mon, 2016-08-08 09:46
Hi Team, I have doubt on number of columns on a table. currently we have 66 columns in a table and we want to add extra 204 columns in that table. My question is it will impact the DB performance and lead to row chaining or not? The DB block si...
Categories: DBA Blogs

SQL query sometimes taking longer (most of the time working good)

Mon, 2016-08-08 09:46
This is the duplicate (unable to edit the old) question where Connor Sir has asked to add code tags but as I was not aware of code tags. Here it is... This is to ask some suggestion about a query which is infrequently taking minutes->hours time...
Categories: DBA Blogs

Why Scalar sub query is reducing my query cost to 50%

Mon, 2016-08-08 09:46
Hi Tom, Please have a look: Below are the 2 queries with the same result set but differing in the cost. When i used scalar sub query instead of inner join its cost is low when compared with the other. Could you please explain. Please u...
Categories: DBA Blogs

Getting data from Multiple Users

Mon, 2016-08-08 09:46
Hi Tom, Please find the below problem statement and kindly provide the solution. Problem Statement: I have a schema and that schema contains a table T1. T1 table contains 2 columns i.e. UserId and CityId. T1 ------------- USERID CITYID -...
Categories: DBA Blogs

RMAN restore into a new database

Mon, 2016-08-08 09:46
Hi.. 3 days before I took Level 0 and Level 1 backup from my database( name:sample) by using RMAN .Yesterday my target database was crashed.So I created another new database(sample1). Now I want to use those backup files and restore that all data in...
Categories: DBA Blogs

Lost control files

Sun, 2016-08-07 15:26
I have couple of questions regarding controlfiles 1.I am connected through catalog and lost all the control files of database how can i recover the database and get all control files back please suggest 2.If in lost of all control files will I be a...
Categories: DBA Blogs

Updating / Resetting columns

Sun, 2016-08-07 15:26
Hey Guys, I have a process where I need to update one column with the value of another if the 1st column is null, when I transfer the value I need to set the value of the second column to null, I am doing this in a single update statement, see exa...
Categories: DBA Blogs

MVIEW Dependencies

Sun, 2016-08-07 15:26
Where are the MVIEW dependencies stored? SQL> create view y1 as select 1 as y1 from dual; View created. <code>SQL> create materialized view y2 as select y1 as y2 from y1; Materialized view created. SQL> create view y3 as select y2 as y...
Categories: DBA Blogs

Selecting rank wise records

Sun, 2016-08-07 15:26
Hi Tom, Its been such a long time to visit here and resolve the doubts but this is the first time I am asking. There is an Employee table with Name, Department, Salary and Date of Joining in it. I need to write a query which will give the outpu...
Categories: DBA Blogs

Oracle Text CONTAINS with NULL input string

Sun, 2016-08-07 15:26
Hi Tom, I am on Oracle 11gR2. I have a table where I have created a text index. I have written a query as below: SELECT * FROM my_transactions t WHERE 1=1 AND CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0; It prompt...
Categories: DBA Blogs

out-of-place refresh of a materialized view

Sun, 2016-08-07 15:26
I am researching how to improve the availability of data in an MV. The doc says that while doing a complete refresh of MV using the out-of-place option that the data in the mv is still available, which I find is true. But it also says the mv is av...
Categories: DBA Blogs

Oracle

Sun, 2016-08-07 15:26
TABLEA ****** C1 C2 C3 (COLS) * - - - * - * - * - * * I have 6(*) in the above table? But when i select my table i get the count 6 result how? sir? From my side i have done like select count(c1) from tb...
Categories: DBA Blogs

plsql trigger

Sun, 2016-08-07 15:26
create or replace trigger foremp before insert on emp declare day char(10); begin if (to_char(sysdate,day) in ('sunday')) then raise_application_error(-20300,'not allowed on sunday'); end if; end; / I created this trigger t...
Categories: DBA Blogs

Are we really create table aumatically as per our requirement

Sun, 2016-08-07 15:26
Hi, I want to create a table automatically as per based of various column data types and various number of column. Please ! dont cconsider the constraint. for example: table name :table1 required output is : DDl structure of crea...
Categories: DBA Blogs

PLSQL syntax

Sun, 2016-08-07 15:26
VARIABLE NAME CANNOT START WITH A) NUMBER B) SPECIAL CHARACTER C) CHARACTER D) ALL OF THE ABOVE I KNOW THAT WE CANNOT START A VARIABLE USING NUMBER ALSO SPECIAL CHARACTERS EXCLUDING(&,#,_). THIS IS A QUESTION I GOT FOR AN EXAM AND I SELECTED...
Categories: DBA Blogs

Is there a way to report on PROCESSES, SESSIONS AND CONNECTIONS that were killed using the kill command?

Sat, 2016-08-06 21:06
Is there a way to report on PROCESSES/SESSIONS/CONNECTIONS that were killed using the kill command?
Categories: DBA Blogs

Timestamp comparison in Oracle

Sat, 2016-08-06 21:06
Hi Guys, I need to fetch almost 2 years old records for 1 day i.e if today is 5 Aug I want to fetch records of 25 July. For this I wrote below query select * from Employee where employee_start_ts between add_months(sysdate-12, -24) and ad...
Categories: DBA Blogs

Pages