Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 44 min 46 sec ago

performance tuning - sql slows down after gather stats

Fri, 2018-05-11 08:06
Hi , I have faced a situation where sql id plan hash value is changed due stats gather on one of table currently i dont understand why this stats gathering cause chnage in plan and due to which execution time is poor now can you guide...
Categories: DBA Blogs

insert into local table with select from multiple database links in a loop

Fri, 2018-05-11 08:06
Hi Tom, i would like to apply the Orignial SQL Statement from Oracle MOS DOC ID 1317265.1 and 1309070.1 for license and healthcheck for all of my database instances. My Goal is to create a centralized repository with informations of my databases. Un...
Categories: DBA Blogs

Impdp not failing even if target table have missing column

Fri, 2018-05-11 08:06
My question why import is not failing even the source and target have different table structure <b>Source DB</b> has below table (with additional column COL3 and populated SQL> desc tab1 Name Null? Type ---------------------------...
Categories: DBA Blogs

Unique key across tables

Thu, 2018-05-10 13:46
Dear tom, How can i enforce unique key across multiple tables. Table1 and Table2 both have ID primary key column. Is it possible to restrict, while inserting and updating into these tables, the union of ID values from two tables are unique. r...
Categories: DBA Blogs

SEQUENCE

Thu, 2018-05-10 13:46
hi tom, during one interview i got one question in sequence i.e if there is one sequnce whose max value is 40,but after got nextval 20.without execute the select query 20,000 times and without alter the sequence i want to get 20,000 in the nextva...
Categories: DBA Blogs

enq: TX - row lock contention wait event for an UPDATE statement

Fri, 2018-05-04 11:06
Hi, We are facing this wait event "enq: TX - row lock contention" when an update statement is being executed.THis update has filter columns which are part of unique index.What I observed during analysis is table has PCTFREE=10 and PCTUSED=NULL in ...
Categories: DBA Blogs

Changes that affect all_object tables

Thu, 2018-05-03 16:46
Hi TOM, Which actions can change the dates on the table all_objects? For example if I debug a package today 5-May-2018 at 17:00:00, the last_ddl_time and timestamp will be updated with the time that the debug was done? Regards, John Juma
Categories: DBA Blogs

error ora-14766

Thu, 2018-05-03 16:46
Dear Tom during a load phase of a table that has a clob I use an "alter table exchange partition" instruction from a stage table and sometimes (and only sometimes, not everytime) during an execution of a view (that insists on another partition of ...
Categories: DBA Blogs

Trigger to clean up associated rows

Thu, 2018-05-03 16:46
Hi Team, I have a table named "t". Please find the structure of the table: CREATE TABLE t ( t_id number(5) , s_id number(5), name char(20), mgr_id number(5), CONSTRAINT pk_t PRIMARY KEY(t_id, s_id) ); insert into t values(1,12,'a',0...
Categories: DBA Blogs

User Password Masking

Wed, 2018-05-02 22:26
Hello, Application user dont want to put the clear text password in the script which in turn connecting to database using this authentication. Is there any way to avoid clear text password and to use encrypted password for login . Regards,...
Categories: DBA Blogs

Why are all table subpartitions going in the same tablespace?

Wed, 2018-05-02 22:26
Dears, I have a problem regarding automatic list partitioning with hash sub-partitioning. the problem is the automatic created hash sub-partitions are not well distributed on the correct table spaces although the first hash-partitions are well dis...
Categories: DBA Blogs

Simple update command keeps running

Wed, 2018-05-02 22:26
hi there i have simple update command <code>UPDATE USERMAST SET USER_LOCK='N' WHERE USER_NAME='rancf';</code> when i execute this query it keeps on running.. USER_ID is the pk for USERMAST table and i tried with USER_ID TOO,same thing is ha...
Categories: DBA Blogs

Different Execution Plan across RAC node

Wed, 2018-05-02 04:06
1. is it possible to have different execution plan across RAC nodes for the same Query ? 2. let's say if i run a query on Node 1 and generated a execution plan named "node1Query1" , will it share between different node ? or it will have another one...
Categories: DBA Blogs

Redo log threads in Real Application Clusters

Wed, 2018-05-02 04:06
Tom, I have been trying to understand the concept of redo log threads in a RAC environment. I understand how the redo logs work in a single instance database. But I cannot find proper documentation anywhere that clearly explains what a redo log thre...
Categories: DBA Blogs

schema table size growth per year

Tue, 2018-05-01 09:46
Hi, As part of capacity planning, customer is asking schema table size growth per year for last 10 years. Is there any way to get it? I tried the below query but through this we are not able to get size per year for last 10 years. SELECT ds....
Categories: DBA Blogs

PRAGMA RESTRICT_REFERENCES - Deprecatedish?

Tue, 2018-05-01 09:46
Hi guys, In the 12.2 PL/SQL Language Reference it says that <code>PRAGMA RESTRICT_REFERENCES</code> is deprecated. http://docs.oracle.com/database/122/LNPLS/RESTRICT_REFERENCES-pragma.htm#LNPLS01339 In the 12.2 Data Cartridge Developers Guide...
Categories: DBA Blogs

VPD predicate executes trice

Tue, 2018-05-01 09:46
Tom, I have a simple implementation of VPD which inserts a record into a log table when a select is performed on the table. I am seeing the log record inserted twice. As you can see from my example below my sql predicate does not limit any d...
Categories: DBA Blogs

Pivoting via SQL dynamically

Mon, 2018-04-30 15:26
SQL puzzle Names table nameData creation script <code> create table Names ( name char (10), primary key (name)); insert into Names values ('Al'); insert into Names values ('Ben'); insert into Names values ('Charlie'); insert into Names ...
Categories: DBA Blogs

SQLLOADER - Error Field in data file exceeds maximum length

Thu, 2018-04-26 20:06
Hi, I am trying to load a data file into a database table using SQL loader. I received the data in an Excel spreadsheet but I saved it as a comma delimited file. When I run the SQL Loader command no records are loaded - looking at the log file I ge...
Categories: DBA Blogs

Is it safe to use ROWID to locate a row?

Mon, 2018-04-23 18:46
Hi Tom, I'm looking at a client application which retrieves several columns including ROWID, and later uses ROWID to identify rows it needs to update: update some_table t set col1=value1 where t.rowid = :selected_rowid Is it safe to do so? ...
Categories: DBA Blogs

Pages