Tom Kyte

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

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

Unable to Find Sample Schema HR Scripts

Mon, 2018-04-23 18:46
Hi Install Oracle 12C on my personal laptop, unfortunately the sample schemas HR,Scott were not created so i tried to Execute below script from command prompt but it did not worked @?/demo/schema/human_resources/hr_main.sql when i checked the loc...
Categories: DBA Blogs

utl_http.begin_request results in protocol error when url size is big

Fri, 2018-04-20 17:26
Hi, while using utl_http package, we are able to make calls to a 3rd party webservice and all was going good till we hit transaction which resulted in big URL size - for ex one transaction had multiple rejections and url size is bigger than normal ...
Categories: DBA Blogs

Upgrade to 12c - High Fetch time vs. Low execution time

Fri, 2018-04-20 17:26
Hi Tom, We are migrating our databases from Oracle 11.2.0.3 to Oracle 12.1.0.2.0R1 on Exadata and after we did this, we are seeing extreme slowness in loading 3 of our application screens, even though the queries are running as or more efficiently...
Categories: DBA Blogs

Partitioning vs Indexing

Thu, 2018-04-19 23:06
Hi Tom, i have a question in partitioning by list of a table. I have a set of tables which need to be hystoricized once a new record is inserted: then i have a STATUS column which flag an active status (AT) and a historic one (ST). To match this re...
Categories: DBA Blogs

Oralce Open v$open_cursor counts simple "updates" as open with the use of a cursor (open, execute, fetch, close, commit)

Thu, 2018-04-19 23:06
I am checking for open cursors while running our client server application (application info below) with the query below and noticed that a simple ?update? without the use of any cursors shows as open cursor. When another ?update? is issued its repla...
Categories: DBA Blogs

Performance issue/session getting hang

Thu, 2018-04-19 23:06
Hi Tom, I have a table having around 5 million records. Table Structure : DESC RPT_MSG_CHANGE <code>Name Null Type ---------- -------- -------------- OID NOT NULL NUMBER PRODUCT NOT NULL VARCHAR2(20)...
Categories: DBA Blogs

Pages