Oracle Interview Questions Part 5

Oracle Interview Questions Part 5

Q41: Describe the logical and physical structure of the Oracle database.

A41: The physical structure of Oracle database contains control file, log files, redo files and data files while the logical structure contains tablespaces, views, tables, etc.


Q42: How is the size of tablespace increased?

A42: Size of tablespace is increased by addition of datafiles to it.


Q43: How is the size of datafiles increased?

A43: Size of datafiles can’t be increased in Oracle 7.0 however for Oracle 7.3 it can be increased using the Resize clause.


Q44: Explain use of control files.

A44: Control files have pointers to locations of various log files, redo files , data files, etc.


Q45: Explain the use of data dictionary.

A45:  Data dictionary is used by Oracle for storing information regarding various logical and physical structures like datafiles, tablespaces, tables, etc.


Q46: Give the advantages and disadvantages of clusters.

A46: Clusters reduce the access time for joins and increases it for insert.


Q47: Can you cluster Long/Long RAW.

A47: No you can’t cluster Long/Long RAW.


Q48: Can you enter null keys in normal index and cluster index?

A48: Yes you can enter null keys in normal index and cluster index.


Q49: How can you use check constraints for self referential integrity?

A49: In a table, a check condition of a column can reference another column in the same table thus providing self referential integrity.


Q50: Give the minimum extents allowed for a rollback extent?

A50: Minimum two extents are allowed for a rollback extent.


Q51: Give the various rollback segment states.

A51: The various rollback segment states are:

a)      Invalid,

b)      Needs recovery,

c)      Partly available,

d)     Offline and

e)      Online.


Q52: Differentiate between primary key and unique key.

A52: Primary key cannot be null however a unique key can be null.


Q53: Can rows be inserted if an insert statement is followed by create table and then by rollback?

Q53: No. Rows cannot be inserted in this way.


Q54: Can multiple savepoints be defined?

A54: Yes. Multiple savepoints can be defined.


Q55: Is a rollback possible to any savepoint?

A55: Yes a rollback is possible to any savepoint.


Q56: What is the maximum limit on the number of columns in a table?

A56: A table can have maximum 254 columns.


Q57: Explain the significance of the & and && operators in PL SQL.

A57: The & operator signifies that a user input  is needed for the PL SQL block variable. The && operator signifies that this variable’s value must be the same as inputted earlier by the user for same variable.


Q58: Can a parameter be passed to a cursor?

A58: Yes. Parameters can be passed to explicit cursors. A cursor parameter could appear wherever a constant appears in a query.



SELECT job, ename FROM emp WHERE sal > median;


Q59: Give the different types of rollback segments.

A59: The different types of rollback segments are:

a)      Private Available to particular instance and

b)      Public Available to all instances


Q60: Can %RowCount be used as a parameter to a cursor?

A60: Yes. %RowCount can be used as a parameter to a cursor.