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 ?
Can views be specified in a trigger statement ?
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
Describe Oracle database's physical and logical
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
What are the advantages of clusters ?
Access time reduced for joins.
What are the disadvantages of clusters ?
The time for Insert increases.
Can Long/Long RAW be clustered ?
Can null keys be entered in cluster index, normal index
Can Check constraint be used for self referential
integrity ? How ?
Yes. In the CHECK condition for a column of a table, we
can reference some other column of the same table and
thus enforce self referential integrity.
What are the min. extents allocated to a rollback extent
What are the states of a rollback segment ? What is the
difference between partly available and needs recovery ?
The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and
What is the difference between unique key and primary
Unique key can be null; Primary key cannot be null.
An insert statement followed by a create table statement
followed by rollback ? Will the rows be inserted ?
an you define multiple savepoints ?
Can you Rollback to any savepoint ?
What is the maximum no. of columns a table can have ?
What is the significance of the & and && operators in PL
The & operator means that the PL SQL block requires user
input for a variable. The && operator means that the
value of this variable should be the same as inputted by
the user previously for this same variable. If a
transaction is very large, and the rollback segment is
not able to hold the rollback information, then will the
transaction span across different rollback segments or
will it terminate ? It will terminate (Please check ).
Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example
below shows. A cursor parameter can appear in a query
wherever a constant can appear. CURSOR c1 (median IN
NUMBER) IS SELECT job, ename FROM emp WHERE sal >
What are the various types of RollBack Segments ?
Public Available to all instances
Private Available to specific instance
Can you use %RowCount as a parameter to a cursor ?
Is the query below allowed :
Select sal, ename Into x From emp Where ename = 'KING'
(Where x is a record of Number(4) and Char(15))
Is the assignment given below allowed :
ABC = PQR (Where ABC and PQR are records)
Is this for loop allowed :
For x in &Start..&End Loop
How many rows will the following SQL return :
Select * from emp Where rownum < 10;
How many rows will the following SQL return :
Select * from emp Where rownum = 10;
Which symbol preceeds the path to the table in the
remote database ?
Are views automatically updated when base tables are
Can a trigger written for a view ?
If all the values from a cursor have been fetched and
another fetch is issued, the output will be : error,
last record or first record ?
A table has the following data : [[5, Null, 10]]. What
will the average function return ?
Is Sysdate a system variable or a system function?
Consider a sequence whose currval is 1 and gets
incremented by 1 by using the nextval reference we get
the next number 2. Suppose at this point we issue an
rollback and again issue a nextval. What will the output
Definition of relational DataBase by Dr. Codd (IBM)?
A Relational Database is a database where all data
visible to the user is organized strictly as tables of
data values and where all database operations work on
What is Multi Threaded Server (MTA) ?
In a Single Threaded Architecture (or a dedicated server
configuration) the database manager creates a separate
process for each database user. But in MTA the database
manager can assign multiple users (multiple user
processes) to a single dispatcher (server process), a
controlling process that queues request for work thus
reducing the databases memory requirement and resources.
Which are initial RDBMS, Hierarchical & N/w database ?
RDBMS - R system
Hierarchical - IMS
N/W - DBTG
What is Functional Dependency
Given a relation R, attribute Y of R is functionally
dependent on attribute X of R if and only if each
X-value has associated with it precisely one -Y value in
Page Numbers :