oracle plsql interview questions

Important Oracle PL SQL interview Questions .

•  What is oracle database ?

 A database is a set of physical files on disk created by the CREATE DATABASE statement .

• What is a database instance ?

A database instance is a set of memory structures that manage database files

What is a record type.
Group of related data items stored in fields.

What is a collection type
Ordered group of elements all of same type


What is type ref cursor
Data type whose value is in memory address of query work area of the database

• What is an index ?

By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table .

• What are triggers ?

triggers are procedures stored in PL/SQL or Java that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.

1. DML
2. DDL

• Implicit attributes of Cursor

1. %FOUND
2. %ISOPEN
3. %NOTFOUND
4. %ROWCOUNT

• What is a DB Link ?

it is a schema object in one database that enables you to access objects on another database .

•  What is a cluster ?

 A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common

• What is a tablespace? 

it is an allocation of space in the database that can contain schema objects.

Types of tablespace .

1. A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in datafiles.
2. An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
3. A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.

• What is SQL*Plus ?

SQL*Plus has a command-line user interface that acts as the client when connecting to the database.

• What is sql loader ?

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.

• Explain normalization.

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.

Terminology :

1. attribute : column

2. candidate key : primary key ( simple or composite )

3. prime attribute : column which is part of primary key

4. non prime attribute : column not part of primary key .

5. super key : any set of attributes which identifies a unique record .

6. functional dependency : B depends on A 

7. transitive dependency : B is dependent of A . C is dependent of B . So C has a transitive dependency on A .

1NF : attribute should be atomic.

2NF : non prime attribute should not depend on a subset of primary key

3NF : non prime attribute should not have transitive dependency with the super key .

BCNF : any functional dependency should be a super key .