Tom Kyte

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

Keep archived logs for 5 days

Thu, 2018-02-01 00:26
Hi, I want to keep archived logs for 5 days in physical location on windows. can you please suggest on this ?
Categories: DBA Blogs

Update with in-list running long

Wed, 2018-01-31 06:26
Hello, I am fairly new with ORacle SQL and am stuck at one point. I am trying to run the following update statement on a daily fact table, and it is running for ever. Is there a way to optimise the below sql, do i need to do any preprocess...
Categories: DBA Blogs

Oracle PLSQL Writing Excel File

Wed, 2018-01-31 06:26
Hi Tom, Thanks for being with us! I have a excel file with a template, what my requirement is to copy that template from that excel file to new excel file and also append some data into the same excel file with template. when I tried to write ...
Categories: DBA Blogs

Any scenairo where Optimizer will not use an active SQL profile

Wed, 2018-01-31 06:26
Hi team, If a SQL query has an active(ENABLED) profile, will there be any scenario when Optimizer will not use it? E.g. As I know the profile is auxiliary statistics on all objects being referenced by underlying SQL query. So if we gather stat...
Categories: DBA Blogs

Cannot enable table locks - ORA-00054: resource busy

Wed, 2018-01-31 06:26
Hello, I have one user who has disabled table locks on a table, and now we can't enable lock on this table : SQL> alter table USER.SOME_TABLE enable table lock; alter table USER.SOME_TABLE enable table lock * ORA-00054: resource busy and a...
Categories: DBA Blogs

TDE Column Enablement

Wed, 2018-01-31 06:26
Hi Oracle Masters, Two questions: 1) I read the below line in "Advanced Security Guide" for TDE: "If you enable TDE column encryption on a very large table, then you may need to increase the redo log size to accommodate the operation". Ho...
Categories: DBA Blogs

Column Creation Time

Wed, 2018-01-31 06:26
Hi Tom, I want to view the time at which a particular column is created/added in the table. USER_TAB_COLUMNS does not contain the column creation/modification timestamp. USER_OBJECTS has LAST_DDL_TIME which is at the Table level. I need Colum...
Categories: DBA Blogs

how to specify block size when installing database silently with dbca

Wed, 2018-01-31 06:26
Hi Tom, I use the script below to create database, but I want to know how to specify db block size.I didnot find a parameter in dbca.rsp corresonding to this. And block size is hard to be changed once the database is created. So can you please tell ...
Categories: DBA Blogs

ORA-24263: Certificate of the remote server does not match the target address

Wed, 2018-01-31 06:26
Dear Ask TOM Team, We have upgrade our DEV Environment to the Oracle 12.2 Release and we are about to finish our upgrade tests. Now one test that we thought should be a simple one is the SEND Mail over the UTL_SMTP package. In the release 12.1 we...
Categories: DBA Blogs

GDPR - backups and database design

Wed, 2018-01-31 06:26
Really surprised to see that nobody had any queries posted on the impacts on backup strategies related to GDPR. There is a clause which says right to erase personal data with respect to the GDPR and we are thinking on the possible ways the backup ...
Categories: DBA Blogs

Open database read only

Tue, 2018-01-30 12:06
What is a command to change DB open mode from read write to read only without restarting the instance
Categories: DBA Blogs

How to find last inserted records from table

Tue, 2018-01-30 12:06
HI TOM, PLEASE EXPLAIN How to find last inserted records from table? IN ORACLE? THANKS BALA
Categories: DBA Blogs

Obtain all JSON keys from JSON columns

Tue, 2018-01-30 12:06
Hello, Is there a way to get all the keys that exist in a JSON column? JSON_VALUE function provides an easy way to access a value for a given key, but is there a way to obtain all the key names? Thanks, Gabi
Categories: DBA Blogs

Multiple SQL Profiles for one SQL

Tue, 2018-01-30 12:06
Is it possible to have multiple SQL Profiles for same query?
Categories: DBA Blogs

Database Performance Different Plans after Database Migration

Tue, 2018-01-30 12:06
I am facing the issue with database performance. Earlier, my database was running on XSeries Platform, Solaris OS. Then I migrated whole data and object to SPARC Platform. Both Servers have a bit different specs. For XSeries, data stores on S...
Categories: DBA Blogs

Re-enable of foreign key constraint takes a long time

Tue, 2018-01-30 12:06
I have two 11.2.0.3 databases on Solaris 11 64bit. They both have the same schemas. One is approx. 1GB and the other is approx. 10GB. On both databases, there is a parent and child table with similar number of records (~300k records each). Fr...
Categories: DBA Blogs

conceptual question regarding ODBC driver

Tue, 2018-01-30 12:06
Hi Tom: I used to work as a analyst who developed SQL queries mainly using SQL developer and SSIS, and recently I have moved to a new team which I have slightly different role , leading me to some questions I never consider before. I am not so sur...
Categories: DBA Blogs

Performance issue while processing Huge XML file

Mon, 2018-01-29 18:06
I'm seeing performance issue while processing the xml file which has more then 10K records, it's working fine if the file has 100 records. Below is the sample procedure I'm using.. <code>++++++++++++++++ CREATE OR REPLACE PROCEDURE process_xml_fi...
Categories: DBA Blogs

Audit logon and logoff of specific users eg sys and system

Mon, 2018-01-29 18:06
Hi, I need to write a trigger to audit logon and logoff activities of specific users 'SYS' and 'SYSTEM'. I have one trigger but its not inserting records into the table mentioned in the trigger. Please, help me to fix the issue.Your help will be...
Categories: DBA Blogs

How to unpivot table data with out displaying column names

Mon, 2018-01-29 18:06
Hi Tom, Am working on Oracle DB and below query <code> select t1.id as dbid, t2.mid as askid, t3.m2idd as amid from table1 t1, table2 t2, table3 t3 where t1.actid = t2.senid and t2.denid = t2.mkid ...
Categories: DBA Blogs

Pages