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:
b) Needs recovery,
c) Partly available,
d) Offline and
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.
CURSOR c1 (median IN NUMBER) IS
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.