DBA Blogs

impdp with multiple REMAP_SCHEMA statements tries to load data twice in the same schema

Tom Kyte - Thu, 2018-02-01 19:06
Dear TOM, We are using impdp Release 11.2.0.3.0 - Production to exchange data between two databases, both in version 11.2.0.3.0 - 64bit Production. We use the following import.par parfile with several REMAP_SCHEMA statements: <code>DUMPFILE=...
Categories: DBA Blogs

No estimate time remainings drop column unused, with checkpoint.

Tom Kyte - Thu, 2018-02-01 19:06
Hi Tom! I had a problem removing a column set unused(version RDBMS 11.2.0.4) Because he was using CHECKPOINT, could not keep track of a running development, as well as estimate in v$SESSION_LONGOPS. For time remaings estimate, I was using an ave...
Categories: DBA Blogs

Few SQL & PL/SQL question(s)

Tom Kyte - Thu, 2018-02-01 19:06
Hi, There are three small questions in this, I wanted to ask in december but you guys were on vacation that's why I could not ask. Q1. What is the exact size restrictions of trigger I have read 32k here k is what is it KB or (32000 bytes or 3276...
Categories: DBA Blogs

SQL request with analytics to fill previous values

Tom Kyte - Thu, 2018-02-01 19:06
Hello, I have a table with this structure table values: <code>(code varchar2(10), date_value date, value number);</code> Example data : <code>'Code1','15/03/2017',25000 'Code1','06/06/2017',26000 'Code1','18/07/2017',29000 'Code1...
Categories: DBA Blogs

where to use metadata table as a plsql developer

Tom Kyte - Thu, 2018-02-01 00:26
Hii friends, how, when, where to use metadata views like ALL_SOURCE, USER_SOURCE, ALL_DEPENDENCIES, USER_DEPENDENCIES, ALL_OBJECTS, USER_OBJECTS. THANKS.
Categories: DBA Blogs

Identify session using Oracle XA

Tom Kyte - Thu, 2018-02-01 00:26
Is there a way to identify if the session executing the PL/SQL application is part of the XA Transaction. We have an application running on WebLogic 12.2.1 using a JDBC Pool. It connects to the database through the Connection Pool. Some of the re...
Categories: DBA Blogs

How to detect where error occurred in PL/SQL

Tom Kyte - Thu, 2018-02-01 00:26
If i'm having a procedure with three select statements while debugging how will u find exactly in which statement u r getting error?
Categories: DBA Blogs

NO_DATA_FOUND exception when passing a empty row in associative array to procedure

Tom Kyte - Thu, 2018-02-01 00:26
I am trying to pass a procedure a record that is, or will be rather a new row in an associative array but am getting a a NO_DATA_FOUND exception. When I set to position in the collection to NULL, it works fine. Example simplified down: -- spec...
Categories: DBA Blogs

Keep archived logs for 5 days

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs