Wednesday, May 11, 2011

Latest Interview QA for PLSQL with basic DBA experience Part 1

Where is the alert log files located on Oracle11g?

The alert.log file normally is in the $ORACLE_BASE//bdump location
Oracle 11g in the database specific location in the diagnostic_dest. Run the below query to find the location,
select value from v$parameter where name='background_dump_dest';

One of the most vexing problems that Oracle DBAs around the world face every day? What is that?

How to resolve that "ORA-01555: snapshot too old" issue?
The common reason is that the Rollback segments are too small. If in AUM (Automatic Undo Management) mode, increase UNDO_RETENTION setting, otherwise use larger Rollback segments.

Info:  In Oracle database during the DML  (Insert / Update / Delete) operations for any changes to the data records, a snapshot of the record before the changes were made is copied to a rollback segment.

What is the main difference between Data Guard and Oracle RAC (Real Application Clusters)?
An Oracle Data Guard configuration can consist of any combination of single-instance and Oracle Real Application Clusters (RAC) multiple-instance databases.

How does one get the value of a sequence into a PL/SQL variable?
One can use embedded SQL statements to obtain sequence values:
select sq_sequence.NEXTVAL into :i from dual;

But direct assignment can be used in Oracle11g, it is a new feature in 11g.
i := sq_sequence.NEXTVAL;

Can create two before_insert trigger on same table?
Oracle allows more than one trigger to be created for the same timing point, but it has never guaranteed the execution order of those triggers.

The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point.

What is Compound trigger?
Compound trigger is one the new feature in Oracle11g. It allows for writing a single trigger incorporating STATEMENT and ROW LEVEL and BEFORE and AFTER.

People who read this post also read :

No comments:

Post a Comment

Thanks for your comments!