DBA Blogs

Difference Between Unique Index and Primary Key Index

Tom Kyte - Fri, 2018-02-02 13:26
IS fetching row(s) using Primary key Index (in where clause) is better then Fetching row(s) using Unique Index (in where clause)? Is there any internal difference between those Unique Index and Primary Key Index?
Categories: DBA Blogs

PL/SQL procedure issue (bulk collect and insert on a dynamic table)

Tom Kyte - Fri, 2018-02-02 13:26
Hi All, Requirement : We need to pass table name as a parameter into the Procedure and also further I need to concatenate that value with a string to make a correct table name which will be available in database. For ex :As per my code , if a...
Categories: DBA Blogs

Lag Analytics Function

Tom Kyte - Fri, 2018-02-02 13:26
Hi Tom, Can you please help me with the below: I have a table like below: <code>Yr Qtr Mth Sales 2010 1 1 1000 2010 1 2 2000 2010 1 3 2500 2010 2 4 3000 2010 2 5 3500 2010 2 ...
Categories: DBA Blogs

How to use ASH report

Tom Kyte - Fri, 2018-02-02 13:26
Hi Tom, Few days back I attended an interview, and one question which I failed to answer properly,was How to use ASH report. I know few things about ASH report like Top user event,Top CPU time and which SQL has spent how much % on which event. But...
Categories: DBA Blogs

Oracle SQL developer's native package???

Tom Kyte - Thu, 2018-02-01 19:06
hi Tom: when I check my ODBC connection under Administration tool, I don't not find any Oracle driver. the strange thing is I can still use Oracle SQL developer to connect to Oracle. I assume we always need a Oracle driver to connect to oracle dat...
Categories: DBA Blogs

date format

Tom Kyte - Thu, 2018-02-01 19:06
Hi Tom, In my pl/sql program I get date in format of string .. say like '2010/06/30 00:00:00' Now I need to convert this into a date and pass it as a parameter to another program.. what is the best way to that.. do i need to read nls_dat...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs