Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 10 hours 41 sec ago

Is there a way to force DML error logging without adding LOG ERRORS INTO clause to INSERT, UPDATE etc?

Thu, 2016-10-13 19:06
For INSERT statements run against a table with an associated DML error log table, I would like to force DML error logging for INSERT statements that do not include a LOG ERRORS INTO clause. For Example: CREATE TABLE "WSB"."TEST" ("CCHAR" VA...
Categories: DBA Blogs

Audit trace for table

Thu, 2016-10-13 19:06
Hi Tom, we came across the request from customers. There were some activities on table (let's name it T1), audit was not turned on from the creation of db. Customer raised request to provide some trace or audit for T1- all activities for pas...
Categories: DBA Blogs

IMPDP STATS TABLES

Thu, 2016-10-13 19:06
Dear Tom, Please help me with the below 1) I did full dump restoration from prod to test environment 2) Now I'm trying to import the prod stats to test environment 3) Created STAT_TABLE in the newly imported test environment like below beg...
Categories: DBA Blogs

How do i insert update and delete using db link

Thu, 2016-10-13 19:06
There are two instance on Unix server Instance A and Instance B i want to update one table on Instance B from Instance A What is the best way ?
Categories: DBA Blogs

DBMS_METADATA issues

Thu, 2016-10-13 19:06
Hi, I am trying to extract the DDL code for an index. Please have a look at the below code. The same returns BEGIN NULL; END; as the output. Please can you confirm if there is something wrong with the code? drop table a; create ta...
Categories: DBA Blogs

SQL query to find employees earning more than department average

Thu, 2016-10-13 19:06
My question.. how to write query to display the employee who are getting more salary then the average salary to the department_number from department table. thanks in advance...
Categories: DBA Blogs

how to improve the SQL performance of an 8 node Exadata Data Warehouse RAC

Thu, 2016-10-13 00:46
Hi Tom! Last day I work with an 8 node Exadata Data Warehouse to tune performance for the slow SQL,and I found that the table is not gather statistics and the execution plan is not the best, it executes with the TABLE ACCESS STORAGE FULL,the table...
Categories: DBA Blogs

change running code's execution plan

Thu, 2016-10-13 00:46
Hi Tom, Suppose a package is executing in prod environment, currently proc A is running and after that Proc B will be executed in next 45 mins. I have just spotted some problems in proc B execution plan(which was generated earlier). I cant chang...
Categories: DBA Blogs

Oracle

Thu, 2016-10-13 00:46
Hi Guys, I am using linux to run the query. I have one query like select * from Emp; I want to spool the results into one file(this file has only exported data without number of rows selected message) and what ever the log i.e number of lines ...
Categories: DBA Blogs

How to replicate a nested table

Thu, 2016-10-13 00:46
I would like to know if there is any easier way to replicate a nested table. Say for a normal table, we can just do CREATE TABLE a_bkp as SELECT * FROM a; Is there a similar easy way to create replica of existing nested table ?
Categories: DBA Blogs

Comments

Thu, 2016-10-13 00:46
Can someone please help me with query for how can I extract the 'comment' section of all the tables ? Used this query to get all the table names: Select table_name, owner, tablespace_name from dba_tables; Used below query to gather the comm...
Categories: DBA Blogs

INDEX MONITORING USAGE

Thu, 2016-10-13 00:46
Hi All, It seems that only indexes that are listed in the explain plan are monitored by index monitoring feature. But indexes that are used for constraint checking might not be listed in explain plan and in this case the index monitoring featur...
Categories: DBA Blogs

Is it possible to find out the problem SQL in a procedure which was executed days ago

Wed, 2016-10-12 06:26
Hi Team, Our system was written by plenty a lot of procedures, and one of them did not performance well. This procedure includes tens of SQL statments, suppose that there's only 1 or 2 SQL statements in the procedure caused this problem. I mean, th...
Categories: DBA Blogs

dbca

Wed, 2016-10-12 06:26
CLSN00107: CRS-5017: L'action de ressource "ora.dbfor.db start" a rencontre l'erreur suivante : ORA-12546: TNS : acces refuse . Pour plus de details, reportez-vous a "(:CLSN00107:)" dans "/exec/products/oracle/grid/log/servfor/agent/ohasd/oraagent_...
Categories: DBA Blogs

Why was my job not running?

Wed, 2016-10-12 06:26
Dear all, I have tried to create a job (wizard) in Oracle SQL Developer 4.0 as follows: ----------SUMMARY------------------ Job Name - TEST Enabled - true Description - Job Class - null Type of Job - PL/SQL Block When to Execute Job - IM...
Categories: DBA Blogs

Unable to get rid of bitmap joins

Tue, 2016-10-11 12:26
Hi Tom For a while I have struggled to optimize response time of SQL below. When executing SQL I get response time above 500 secs and more than 1 mio. consistents gets in auto trace. select * from sag s join table2 ms on (ms.f...
Categories: DBA Blogs

how to trap unique id of record with error using <SQL%BULK_EXCEPTIONS/>

Tue, 2016-10-11 12:26
From <SQL%BULK_EXCEPTIONS/> we can find out the error_index. This does not allow us to identify the the particular record with error. We should be able to trap the unique id (primary key or whatever) of the record. Only this will allow us to pinpoint...
Categories: DBA Blogs

Capturing DDL changes on a Table

Tue, 2016-10-11 12:26
I am thinking of creating a utility Proc that will capture all Development DDL changes from the Database. This utility will baseline all DDl's for a given release say R1 and while we are developing for release R2 The utility will create the increme...
Categories: DBA Blogs

Identify the Users who had modified Database Objects

Tue, 2016-10-11 12:26
Hi Tom, Please can you help me to identify the easiest method to figure out the users who had modified a Database Object like tables,indexes etc. I am a new DBA and would be very helpful if you could advice. Thanks, Vish
Categories: DBA Blogs

appendChildXML with parent-namespace in the child

Tue, 2016-10-11 12:26
Hi Chris & Connor I want to append child nodes to an XML but have problems because of the (parent)namespace in the children: The child node in appendchildxml() is not a valid XML because of the namespace that comes from the parent. The XML shou...
Categories: DBA Blogs

Pages