Oracle Interview Questions and Answers

What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

What is the maximum no. of statements that can be specified in a trigger statement ?
One.

Can views be specified in a trigger statement ?
No

What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value

What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.

What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.

What are constraining triggers ?
A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.

Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.

Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.

What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.

What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc