Sunday, May 2, 2010
Q: What are mutating triggers?
A: A trigger giving a SELECT on the table on which the trigger is written.
Q: What are constraining triggers?
A: A trigger giving an Insert / Update on a table having referential integrity constraint on the triggering table.
Q: Describe Oracle database's physical and logical structure?
A: Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
Q: Can you increase the size of a tablespace? How?
A: Yes, by adding datafiles to it.
Q: Can you increase the size of datafiles? How?
A: Yes (by using the Resize clause).
Q: What is the use of Control files?
A: Contains pointers to locations of various data files, redo log files, etc.
Q: What is the use of Data Dictionary?
A: Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc
Q: What are the advantages of clusters?
A: Access time reduced for joins.
Q: What are the disadvantages of clusters?
A: The time for Insert increases.
Q: Can Long/Long RAW be clustered?
Q: Can null keys be entered in cluster index, normal index?
Q: Can Check constraint be used for self referential integrity? How?
A: 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.
Q: What are the min. extents allocated to a rollback extent?
Q: What are the states of a rollback segment? What is the difference between partly available and needs recovery?
A: The various states of a rollback segment are :
· PARTLY AVAILABLE,
· NEEDS RECOVERY and
Q: What is the difference between unique key and primary key?
A: Unique key can be null; Primary key cannot be null.
Q: An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted?
Q: Can you define multiple savepoints?
Q: Can you Rollback to any savepoint?
Q: What is the maximum no. of columns a table can have?
Q: What is the significance of the & and && operators in PL SQL?
A: 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.
Q: 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?
A: It will terminate (Please check ).
Q:Can you pass a parameter to a cursor?
A: 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 > median;
Q: What are the various types of RollBack Segments?
A: Public Available to all instances
Private Available to specific instance
Q: Can you use %RowCount as a parameter to a cursor?
Q: 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))
Q: Is the assignment given below allowed :
ABC = PQR (Where ABC and PQR are records)
Q: Is this for loop allowed :
For x in &Start..&End Loop
Q: How many rows will the following SQL return :
Select * from emp Where rownum < 10;
A: 9 rows
Q: How many rows will the following SQL return:
Select * from emp Where rownum = 10;
A: No rows
Q: Which symbol preceeds the path to the table in the remote database?