Oracle Interview Questions – Part 42

Q.What is correlated sub-query?     

A.In a simple SubQuery, the result retrieved by the inner query is fed to the outer query. The outer

query takes the result as its input and processes it to produce its output.However, in a corelated sub

query, a correlated sub-query is dependent upon the outer query.The outer query and the sub-query

are related typically through a WHERE statement located in the sub-query.The sub query gives a

reference to the outer query. Then the outer query executes and the result is returned to the sub

query. Finally the sub query is executed for every row that is selected by the outer query.

Q.Explain drop and truncate table command.     
A.TRUNCATE removes all rows from a table.The operation cannot be rolled back and no triggers will

be fired.The following example will show what a TRUNCATE does:
Table truncated.
The DROP command removes a table from the database. All the tables’ rows, indexes and privileges

will also be removed. No DML triggers will be fired. The operation cannot be rolled back.The following

example will show what a DROP does:
Table dropped.

Q.What is trigger in oracle?     

A.Triggers are constructs in PL/SQL that need to be just created and associated with a table. Once

they are created, when the table associated with it gets updated due to an UPDATE, INSERT or a

DELETE, the triggers get implicitly fired depending upon the instructions passed to them.

Q.What are the types of triggers?      

A.The types of triggers are:
1.Row level triggers
2.Statement level triggers
3.BEFORE and AFTER triggers

Q.What is the function of SMON? 
A.The SMON background process performs all system monitoring functions on the oracle

database.Each time oracle is re-started, SMON performs a warm start and makes sure that the

transactions that were left incomplete at the last shut down are recovered. SMON performs periodic

cleanup of temporary segments that are no longer needed.

Q.what are the different types of segment.
A.There are four types of segments used in Oracle databases: segments
2.index segments
3.rollback segments
4.temporary segments

Q.what are temporary segments?
A.Temporary Segments:
The SELECT statements need a temporary storage. When queries are fired, oracle needs area to do

sorting and other operation due to which temporary storages are useful.The commands that may use

temporary storage when used with SELECT are:GROUP BY, UNION, DISTINCT, etc.

Q.Explain SGA memory structures: Shared Pool, Database buffer Cache, Redo log Cache, Large Pool

Java Pool.

A.SGA (System Global Area) is a dynamic memory area of an Oracle Server. In SGA,the allocation is

done in granuels. The size of the SGA is dependent on SGA_MAX_SIZE parameter.The memory

structures contained by SGA are:-
1.Shared Pool – This memory structure is divided into two sub-structures which are Library Cache and

Data Dictionary Cache for storing recently used PL/SQL statements and the recent data definitions. The

maximum size of the Shared Pool depends on the SHARED_POOL_SIZE parameter.
2.Database Buffer Cache – This memory structure improves the performance while fetching or

updating the recently used data as it stores the recently used datafiles. The size of this block is

decided by DB_BLOCK_SIZE.
3.Redo Log Buffer – This memory structure is used to store all the changes made to the database and

it’s primarily used for the data recovery purposes. The size of this block is decided by LOG_BUFFER.
4.Java Pool – This memory structure is used when Java is installed on the Oracle server. Size that can

be used is stored in parameter named JAVA_POOL_SIZE.
5.Large Pool – This memory structure is used  to reduce the burden of the Shared Pool, as the Session

memory for the Shared Server, as the temporary storage for the I/O and for the backup and restore

operations or RMAN. Parameter that stores the maximum size is LARGE_POOL_SIZE.

Q.What is SYSTEM tablespace?

A.SYSTEM tablespace is automatically created when the database is created. It contains data dictionary

for the entire database.

Q.What is the use of Data Dictionary in oracle?

A.Data Dictionary is used to store information about various physical and logical Oracle

structures.e.g.Tables, Tablespaces, datafiles, etc.

Q.What are elements of database logical layers?

A.The logical layer of the database consists of the following elements:
One or more tablespaces.The database schema that comprises of items such as tables, clusters,

indexes, views, stored procedures, database triggers, and sequences.

Q.What is Data-Dictionary Cache?

A.Data-Dictionary Cache keeps information about the logical and physical structure of the database.
The data dictionary contains information such as the following:
1.User information, such as user privileges
2.Integrity constraints defined for tables
3.Names and data types of all columns in database tables
4.Information on space allocated and used for schema objects

Q.Explain how to DISABLE and ENABLE constraint.

A.Disable a constraint by using the DISABLE clause.Enable a constraint by using the ENABLE clause.

Q.What are the different Levels of Auditing? Explain them

1.Statement Auditing
2.Privilege Auditing
3.Object Auditing.

Q.What dynamic data replication?

A.Dynamic data replication is the way in which updating or inserting records in remote database

through database triggers.

Q.What is Two-Phase Commit?

A.Two-Phase Commit has two phases, a prepare phase and a commit phase.It is the mechanism

ensuring a distributed transaction either commits on all involved nodes or rolls back on all involved


Q.Write a PL/SQL program for a function returning total tax collected from a particular place.

A.PL/SQL program
Create of Replace Function Tax-Amt
Place varchar2,
Return Number is
Place_wise_tot_tax : = 0;
Select sum(TaxAmt) from Tax where location = Place;
Place_wise_tot_tax = TaxAmt;
Return Place_wise_tot_tax
End Tax-Amt What is clusters?
Clusters group together tables that share common columns. These tables are often used together.

Q.What are the types PL/SQL code blocks?

A.1.Anonymous Block
It is a block of codes without a name.It may contain a declaration part, an execution part, and

exception handlers.Stored Program Unit
It is a block of codes with a name.It is similar to an anonymous block just that it can take parameters

and return values.
It is a block of code that is implicitly fired based some specific event.

Q.Describe how to load data through External Tables.

A.Create an external table with columns matching data fields in the external file.Create a similar

table.Execute INSERT INTO … SELECT statement to load data from the external file.

Q.What are the database objects in oracle?

A.Components of logical database structure of Oracle database are:
2.Database’s schema objects.

Q.What is difference between UNIQUE and PRIMARY KEY constraints?

A.A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. Primary

Key doesn’t allow NULL value whereas UNIQUE key allows NULL value.

Q.WHERE clause vs. HAVING clause.

A.HAVING clause is used with group function and it is written after GROUP BY clause. The WHERE

clause is used when you want to specify a condition for columns. If WHERE clause is used with GROUP

BY, it is used before GROUP BY clause.

Q.What is locking, advantages of locking and types of locking in oracle?

A.Locking protect table when several users are accessing the same table. Locking is a concurrency

control technique in oracle. It helps in data integrity while allowing maximum concurrency access to

data. Oracle offers automatic locking whenever situation requires. This is called implicit locking.
Types of locking
Shared Lock
This type is placed on a record when the record is being viewed.
Exclusive lock
This is placed when Insert, Update or Delete command is performed. There can be only one exclusive

lock on a record at a time.

Q.What are background processes in oracle?

A.Oracle uses background process to increase performance.
Database writer, DBWn
Log Writer, LGWR
Checkpoint, CKPT
System Monitor, SMON
Process Monitor, PMON
Archiver, ARCn

Q.What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

•When a SQL statement raises an exception, Oracle captures the error codes by using the SQLCODE

and SQLERRM globally-defined variables.
•SQLCODE and SQLERRM can track exceptions that are handled by the OTHERS clause of the

exception handler.
•SQLCODE returns the current error code from the error stack and the error message from the current


Q.What is tkprof and how is it used?

•Tkprof is a performance diagnosing utility available to DBAs. It formats a trace file into a more

readable format for performance analysis. So that the DBA can identify and resolve performance

issues like poor SQL, indexing, and wait events.
•When a user comes across problems related to the response time in comparison to his previous

weeks response time, Session tracing and tkprof can be used to see exactly what is happening on the

database, enabling the DBA to take corrective action.
•At times, viewing SQL that is being executed for an application will be the only mechanism a DBA

will have. These situations arrive during the execution of encrypted PL/SQL code on the database or

submission of SQL statements from third party applications.

Q.Explain the different types of queries in Oracle.

Session Queries are implicitly constructed and executed by a Session based on input parameters.Input

parameters are used to perform the most common data source actions on objects.
Database Queries are created and then executed to perform any data source action on either objects or

data. They can be further refined by also creating and configuring its Call.
Named Queries can be executed repeatedly by name. They are an instance of DatabaseQuery stored by

name in a Session or a descriptor’s DescriptorQueryManager where it is constructed and prepared

Call Queries are instances of Call that are created and then either executed directly or indirectly in the

context of a DatabaseQuery.
Redirect Queries are instances of MethodBasedQueryRedirector set on a named query. When the query

is executed, the static method is invoked.
Historical Queries are queries executed in the context of a historical session using the time-aware

features of the TopLink Expression framework.
Interface and Inheritance Queries are queries that references an interface type or super and subclasses

of an inheritance hierarchy.

Q.What is Nested Table?

A.Nested Table is a table inside a table. It can have several rows for each row of its parent table.
Name the components of physical database structure of Oracle database.
Redo log files
Control files
Name the components of logical database structure of Oracle database.
Database’s schema objects.

Q.What is a Tablespace?

A.Tablespaces is a logical storage unit. It is used to group related logical structures together.

Q.What is Rollback Segment in oracle?

A.Rollback Segment in oracle is used to store “undo” information temporarily.

Q.Define a profile in oracle?

A.A profile is assigned to each database user that states its limitation on various system resources.

Q.What dynamic data replication?

A.Dynamic data replication is the way in which updating or inserting records in remote database

through database triggers.

Q.What are elements of database logical layers?

A.The logical layer of the database consists of the following elements:
One or more tablespaces.
The database schema that comprises of items such as tables, clusters, indexes, views, stored

procedures, database triggers, and sequences.

Q.Describe Program Global Area (PGA)?

A.The PGA is a memory area that contains data and control information for the Oracle server

processes. This area consists of the following components:
Stack space–This holds the session’s variables and arrays.
Session information–If you are not running the multithreaded server, the session information is

stored in the PGA. If you are running the multithreaded server, the session
Private SQL area–This area keeps information about binding variables and runtime buffers.

Q.Describe Oracle architecture in brief ?

A.The Oracle database has:
-Logical layer: The components of the logical layer map the data to these physical components
-Physical layer: The physical layer consists of the files that reside on the disk
Tablespaces and Datafiles
The database is divided into one or more logical pieces known as tablespaces. A tablespace is used to

logically group data together
The Database Schema: A collection of logical-structure objects, known as schema objects.
These schema objects consist of structures such as:
-stored procedures,
-database triggers, and
Segments, Extents, and Data Blocks
Within Oracle, the space used to store data is controlled by the use of logical structures. These

structures consist of the following:
•Data blocks–A block is the smallest unit of storage in an Oracle database. (contains header

information concerning the block itself as well as the data)
•Extents–Extents consist of data blocks.
•Segments–A segment is a set of extents used to store a particular type of data.
The Oracle Instance has:
•User processes
•Oracle background processes
•The shared memory
The Oracle Memory Structure contains:
•The System Global Area (SGA)
•The Data-Dictionary Cache
•The Program Global Area (PGA)


A.These are the built in large object data types.
•BLOB, CLOB, NCLOB are stored internally where as BFILE is stored externally
•The BLOB data type stores binary large objects.
•The CBLOB data type stores character large objects.
•The NCBLOB data type stores character large objects in multibyte national character set.
•The BFILE data type enables access to binary file LOBs that are stored in file systems outside the

Oracle database.
All these data types have a capacity of 4GB

Q.What are difference between post database commit and post-form commit?

It fires once during the Post and Commit Transactions process, after the database commit occurs.
It fires after Oracle Forms issue the Commit to finalize the transaction.
The Post-Forms-Commit
It fires once during the Post and Commit Transactions process.
If there are records in the form that have been marked as inserts, updates, or deletes, this trigger fires

after these changes have been written to the database but before Oracle Forms issues the database

Commit to finalize the transaction.
If the operator or the application initiates a Commit when there are no records in the form have been

marked as inserts, updates, or deletes,then Oracle Forms fires these, without posting changes to the


Q.What is DML command?

A.Using DML command, you can create new records, update records and delete records. The following

commands fall in this category.

Q.Explain the types of Exceptions ?

A.Predefined oracle exceptions
These are the PL/SQL runtime engine raised named system exceptions. These exceptions are raised

automatically by the program. You cannot control when PL/SQL will raise a system exception.
User-defined exceptions
These are programmer raised named exceptions. The programmer can use an explicit call to the RAISE

statement to raise a programmer-defined or system-named exception. The programmer can also raise

unnamed, programmer-defined exceptions and re-raised “current” exceptions.

Q.What is the content view and stacked view?

A.In a window that is displayed, the content view occupies the entire content pane.
A stacked view is not the base view for the window to which it is Assigned. However, content view is

the base view and this is how it differs from the stacked view.

Q.Explain the different types of canvas views.

A.There are three types of canvas views:-
1.Stacked Canvas Views
2.Content Canvas Views
3.Horizontal/Vertical Toolbar Canvas Views

Q.What is the purpose of Synonym in oracle?

A.Synonym provide alias name for a table.
Synonym hides name of the table.
With Synonym, you can provide users with less complicated table name than the real one.

Q.What are the types of Join?



This site uses Akismet to reduce spam. Learn how your comment data is processed.