Tom Kyte

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

RMAN backup

Thu, 2016-11-17 03:06
Hi,guy! I use RMAN to backup the database on Nov 13 through the COMMVAULT software,but on Nov 15,the oracle server CPU is high,then I connect to the server and check,found that the wait event is 'RMAN backup&rcovery I/O',and the logon time is the ...
Categories: DBA Blogs

Update query in after insert trigger

Thu, 2016-11-17 03:06
Hello Tom- my requirement is When a record is inserted into ERR table, a post-event trigger will fire updating the D field to accept sys date (3 columns has to update in my original requirement). I've written the below code, however after the recor...
Categories: DBA Blogs

Last DML Operation timestamp on Production

Thu, 2016-11-17 03:06
Hi, I need to get timestamp of last DML operation performed on table from Production. I tried using - SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) from mytablename; SELECT MAX(SCN_TO_TIMESTAMP(ORA_ROWSCN)) from mytablename; Non of them worked...
Categories: DBA Blogs

Number of rows of a value from an unbounded, ignore nulls NEXT_VALUE or FIRST_VALUE

Wed, 2016-11-16 08:46
I understand the sql in the LiveSQL link. What I am wondering is how I find the number of rows the analytic function has to look to get the last/next value if IGNORE NULLS is part of the statement. If I look at the second row (REPORT_MONTH = 01-FE...
Categories: DBA Blogs

Synchronize specific data based on date using DBMS_COMPARISON

Wed, 2016-11-16 08:46
Hi Tom, I have already synchronizing data successfully between remote table and local table using DBMS_COMPARISON with scan mode FULL. But the synchronization performance very slow as of data growth. When I read the documentation, there is n...
Categories: DBA Blogs

database block and redo blocks

Wed, 2016-11-16 08:46
1.how the same blocks are copy/exist(while update transactions) in database block buffer and redolog buffer 2.which one (DBBC and RLB) is first got activated
Categories: DBA Blogs

Mismatch in Execute and Fetch of execution plan of SQL

Wed, 2016-11-16 08:46
Hi, I have below trace information for a SQL in tkprof trace file call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0...
Categories: DBA Blogs

Alter table add columns provided from difference of columns from another table.

Wed, 2016-11-16 08:46
Hi, There are two tables A and B with identical columns at first. B is a backup table of A that updated from time to time. Table A were altered and added some columns with varchar2/date data types. I can get the columns, data types and length...
Categories: DBA Blogs

Before Delete trigger and prevent deletion. Track the entry in EVENT_DETAILS table

Wed, 2016-11-16 08:46
Good Afternoon Tom..!! My requirement is when user trying to delete a record from T1 table. If an attempt to delete an entry from T1 is captured, create an entry in the EVENT_DETAILS and the record will not be deleted from T1. When rollback, EVENT_D...
Categories: DBA Blogs

Roll back

Wed, 2016-11-16 08:46
Hi, I would like to know about rollback and commit.do they commit or roll back the current session data or all the current session data on data base? For example a user inserted 10 rows and b user inserted 15 rows and b performed roll back then is...
Categories: DBA Blogs

Partitioning for Existing tables in 12c

Wed, 2016-11-16 08:46
Dear Experts, Thanks a lot for your advise and support to the needy. I am in process of partitioning on DATE INTERVAL on existing table which holding the data. 1. Application team wants to partition for future data and no need to partition t...
Categories: DBA Blogs

Caching in memory

Wed, 2016-11-16 08:46
I would like to know how much amount of data gets stored in the buffer cache? Say, if I have huge amounts of RAM on the server, and say If I increase SGA proportionately, will my buffer cache high amount of data near to that of RAM allocated? What...
Categories: DBA Blogs

oracle form builder with pl/sql

Wed, 2016-11-16 08:46
Hello tom, I have a comprhensively large data in pl/sql for which i created a view for report purposes I am using oracle form builder for the first time. I wanted to show user data from my view based on the date range he/she selects Can you h...
Categories: DBA Blogs

difference between varchar2(10) and varchar2(10 char) in oracle

Tue, 2016-11-15 14:26
Hi team, Could you please explain the difference between the below two data types : difference between varchar2(10) and varchar2(10 char) in oracle asktom I know varcha2(10 char) , we can use in multibyte characters. So could you pleas eexp...
Categories: DBA Blogs

Backup and restore

Tue, 2016-11-15 14:26
Hi team, I have 11g Production database logical backup export and When development team required we import it into the reporting database. But now we have migrated 11g database to 12c with ASM and same logical backup export is every day. So, ...
Categories: DBA Blogs

How to insert the data using sql*loader by CSV file which contain comma as separator and comma present at column value

Mon, 2016-11-14 20:06
Hi Connor, I have an issue with sql*loader during loading below CSV file I have a csv file with below data:- Column names:- empid,empname,address,salary,deptn0 CSV file data:- 1123,Swarup,PO Box 42,1407 Graymalkin Lane,Salem Center, N...
Categories: DBA Blogs

Data Guard Log Apply method

Mon, 2016-11-14 20:06
I have a primary and a standby database which is running in maximum performance mode and LGWR ASYNC has been set for the same in Primary. Platform - Linux and Version - 12c This is regarding the apply process in Standby Database 1. I do not ...
Categories: DBA Blogs

Sql statistics per execution

Mon, 2016-11-14 20:06
Hi Tom, Is there a way to find cpu_time, db_time, physical_read_requests, physical_write_requests...etc per execution basis ? Say I run a particular SQL multiple times with different bind values. I'm interested in seeing sql with bind variables ...
Categories: DBA Blogs

Oracle Tracing with Bind Variables

Mon, 2016-11-14 20:06
Hi , I enabled tracing on the particular session in oracle database by using "dbms_system.set_sql_trace_in_session" and i am not enabled to trace back the binding variables associated with insert statements . Below is the sample statement: i...
Categories: DBA Blogs

Validation procedure

Mon, 2016-11-14 01:46
How to write a validation procedure for three conditions which are dependent on each other . first condition will be for country zone in that there will be condition for branch for specific date range .
Categories: DBA Blogs

Pages