Oracle Interview Questions – Part 20

Q.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.

Q.What are the min. extents allocated to a rollback extent?


Q.What is the significance of the & and && operators in PL SQL?

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 ).

Q.What is the difference between unique key and primary key?
Unique key can be null; Primary key cannot be null.

Q.Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes, database trigger would fire.

Q.Can you create index on view?

Q.Can you alter synonym of view or view?

Q.What is the difference between a view and a synonym?
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.

Q.What is the difference between alias and synonym?
Alias is temporary and used with one query. Synonym is permanent and not used as alias.

Q.What is the effect of synonym and table name used in same Select statement?

Q.An insert statement followed by a create table statement followed by rollback? Will the rows be inserted?
No. you define multiple savepoints?

Q.Can you Rollback to any savepoint?

Q.What is the maximum no. of columns a table can have?

Q.Can you use %RowCount as a parameter to a cursor?

Q.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 :

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;

9 rows

Q.How many rows will the following SQL return :
Select * from emp Where rownum = 10;
No rows

Q.Which symbol preceeds the path to the table in the remote database?

Q.Are views automatically updated when base tables are updated?

Q.Can a trigger written for a view?

Q.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?

Last Record

Q.A table has the following data : [[5, Null, 10]]. What will the average function return?


Q.What’s the length of SQL integer?
32 bit length

Q.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 > median;

Q.What are the various types of RollBack Segments?

Public Available to all instances
Private Available to specific instance

Q.Is Sysdate a system variable or a system function?

System Function

Q.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 be?

Q.Which are initial RDBMS, Hierarchical & N/w database?

RDBMS – R system
Hierarchical – IMS

Q.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 R

Q.What is Auditing?

The database has the ability to audit all actions that take place within it.
a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL

Q.While designing in client/server what are the 2 imp. things to be considered?
Network Overhead (traffic), Speed and Load of client server

Q.When to create indexes?

To be created when table is queried for less than 2% or 4% to 25% of the table rows.

Q.How can you avoid indexes?
TO make index access path unavailable – Use FULL hint to optimizer for full table scan – Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. – Use an expression in the Where Clause of the SQL.

Q.What is the result of the following SQL :
Select 1 from dual
Select ‘A’ from dual;

Q.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 these tables.

Q.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.