Oracle interview questions - April 17, 2013 at 01:16 PM
by Kshipra Singh
1. Explain: a.) Integrity Constraints
It can be called as a declarative way in order to define a business rule for a
- It can be called as an optional structure which is associated with a table for
direct access to the rows
- Index can be created for one or more columns in a table
- It can be defined as a specific number of contiguous data blocks in single
- It is used to store a specific type of information.
2. What is ANALYZE command used for?
ANALYZE command is used to perform various functions on index, table, or
cluster, as listed below:
- It helps in dentifying migrated and chained rows of the table or cluster.
- It helps in validating the structure of the object.
- It helps in collecting the statistics about object used by the optimizer.
They are then stored in the data dictionary.
- It helps in deleting statistics used by object from the data
- Rename - It is a permanent name provided to a table or column.
- Alias - It is a temporary name provided to a table or column which gets over
after the execution of SQL statement.
b.) What is a view?
- It is virtual table which is defined as a stored procedure based on one or
5. a.) What are the varoius components of physical database structure of Oracle
Oracle database comprises of three kinds of files:
- Redo log files,
- Control files.
b.) List out the components of logical database structure of Oracle database.
- Database's schema objects.
6. a.) What do you mean by a tablespace?
- These are the Logical Storage Units into which a database is divided.
- It is used to group together the related logical structures.
b.) What is Control File used for?
Control File is used for:
- Database recovery.
- Whenever an instance of an ORACLE database begins, its control file is used
to identify the database and redo log files that must be opened for database
operation to go ahead.
7. a.) What is a synonym? What are its various types?
A synonym can be called as an alias for a table, view, sequence or program unit.
It is basically of two types:
- Private - Only the owner can access it.
- Public - Can be accessed by any database user.
b.) What are the uses of synonyms?
- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote
- Simplify the SQL statements for database users.
8.) What do you mean by a deadlock?
- When two processes are waiting to update the rows of a table which are locked
by another process, the situation is called a deadlock.
- The reasons for it to happen are:
- lack of proper row lock commands.
- Poor design of front-end application
- It reduces the performance of the server severely.
- These locks get automatically released automatically when a commit/rollback
operation is performed or any process is killed externally.
9.) a.) What suggestions do you have to reduce the network traffic?
Following are some of the actions which can be taken to reduce the network
- Use snapshots to replicate data.
- Use remote procedure calls.
- Replicate data in distributed environment.
b.) What are the various types of snapshots ?
There are two types of snapshots:
- Simple snapshots - Based on a query that does not contain GROUP BY clauses,
CONNECT BY clauses, JOINs, sub-query or snashot of operations.
- Complex snapshots- which contains atleast any one of the above.
10.) What is a sub query? What are its various types?
- Sub Query also termed as Nested Query or Inner Query is used to get data from
- A sub query is added in the where clause of the main query.
There can be two types of subqueries:
a.) Correlated sub query
- It can reference column in a table listed in the from list of the outer query
but is not as independent as a query. .
b.) Non-Correlated sub query
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query
11.) Will you be able to store pictures in the database?Explain.
- Yes, pictures can be stored in the database using Long Raw Data type.
- This datatype is used to store binary data for 2 gigabytes of length.
- However, the table can have only one Long Raw data type.
12.) Explain: a.) Integrity constraint.
- It is a declaration defined in a business rule for a table column.
- Integrity constraint ensures the accuracy and consistency of data in a
- It is of three types - namely - Domain Integrity, Referential Integrity and
b.) COALESCE function
- This function is used to return the value which is set not to be null in the
- Incase all values in the list are null the coalesce function will return
- Its representation:
Coalesce(value1, value2, value3,…)
13.) Explain the following: a.) BLOB datatype.
- It is a data type with varying length binary string, used to store two
- For BLOB, the length needs to be specified in bytes.
- DML - it is also termed as Data Manipulation Language (DML).
- It is used to access and manipulate data in the existing objects.
- DML statements are insert, select, update and delete.
14.) Differentiate between: a.) TRANSLATE and REPLACE.
- Translate is used to substitute character by character.
- Replace is used to substitute a single character with a word.
b.) What is Merge Statement used for?
- Merge statement is used to select rows from one or more data source to
updating and insert into a table or a view.
15.) What are the various Oracle Database objects?
Various database objects are as follows:
- Tables – This is a set of elements organized in vertical and horizontal
- Tablespaces – This is a logical storage unit in Oracle.
- Views – It is virtual table derived from one or more tables.
- Indexes – This is a performance tuning method to process the records.
- Synonyms – This is a name for tables.
16. What is the purpose of Save Points in Oracle database?
- Save Points are used to divide a transaction into smaller phases.
- It enables rolling back part of a transaction.
- Maximum 5 save points are allowed in Oracle Database.
- Whenever an error is encountered, it is possible to rollback from the point
where the SAVEPOINT has been saved.
17. a.) What is an ALERT?
- It a window which appears on the screen overlaying a portion of the current
b.) Differentiate between post-database commit and post-form commit.
- The post-database commit trigger is fired after oracle forms issue the commit
to finalized transactions.
- The post-form commit is fired during the post and commit transactions
process, after the database commit occurs.
18. Differentiate between pre-select and pre-query.
- Once oracle forms construct the select statement to be issued Pre-select is
fired during the execute query and count query processing. All this happens
before the statement is actually issued.
- The pre-query trigger is fired just before oracle forms issue the select
statement to the database.
19. What is hot backup and logical backup?
- Backing up the archive log files when database is open is called Hot backup.
- To do this, the ARCHIVELOG mode is enabled.
- Following files are backed up - All data files, Archive log, redo log files
and control files.
- Logical back ip is reading a set of database records and writing them into a
- An Export utility is required to take the backup while an Import utility is
required to recover from the backup.
20. What do you mean by Redo Log file mirroring ?
- The process of having a copy of redo log files is called mirroring.
- It is done by creating group of log files together. This ensures that LGWR
automatically writes them to all the members of the current on-line redo log
- In case a group fails, the database automatically switches over to the next
group. It diminishes the performance.
Latest answer: Trigger in act which is performed automatically
before or after a event occur. Stored procedure is a set of functionality which
is executed when it is explicitly invoked................. Read
Latest answer: Stored procedure is a set of pre-compiled SQL
statements, executed when it is called in the program. Triggers are similar to
stored procedure except it is executed automatically when any operations are
occurred on the table.................. Read
Latest answer: A Trigger that contains statements which cause
invoking of other Triggers are known as cascading triggers. Here’s the order of
execution of statements in case of cascading triggers:................ Read answer
Oracle interview questions and answers for freshers and experienced
What is Rational database? Flat database vs. Rational database
Features of Rational database
Dr. Codd defined thirteen standards which must be met before a database can be
considered to be a relational database. What was that?
Explain how data is arranged in a rational database with an
Overview of Data Manipulation Language
Explain how to retrieve information using SELECT.
Add information using INSERT
Change information using UPDATE
Remove information using DELETE
Use Subqueries as your search condition............
What are aggregate functions in SQL? What are those functions?
Using ROLLUP to aggregate data in SQL
Define, syntax and an example with output for:SUM function, AVG (average)
function, COUNT function, Max and Min function..............
Explain subqueries with an example - A subquery is a query within a query. The
inner query is processed first. They are most commonly used to return data from
multiple tables when the exact value is not known...............
What is SQL*Plus? Explain its features
Explain how to use the SQL*Plus utility.
What are the basic SQL*Plus commands?
What is AFIEDT.BUF?
Difference between ? and HELP.
Explain how to enable the SQL*Plus HELP facility.
Difference between @ and @@.
Difference between & and &&.
Difference between ! and HOST
How can one prevent SQL*Plus connection warning messages?............
What is a Database Table?
How many types of tables supported by Oracle? Explain them.
Can you explain how to create a aew table in your schema?
Explain with an example how to create a new table by selecting rows from
Depict a sample script for renaming an existing table.
Sample script to drop an existing table.
How to Add a new column to an existing table?...........
Oracle constraints - overview. What are the types of constraints avaialable in
Explain Oracle "Check" constraint.
Explain Not Null constraint.
Explain primary key constraint.
Explain oracle foreign key constraint.
Explain unique Constraint.
Explain with an example how to alter oracle table constraint..........
Explain how to limit the rows that are retrieved by a query.
Explain how to sort the rows that are retrieved by a query.
What are the comparison operators in oracle. List them with
What is a Collection?
Nested Tables vs. Associative Arrays
Nested Tables vs. Varrays
Explain the methods exist for collections. - EXISTS , COUNT , LIMIT , FIRST and
LAST , PRIOR and NEXT , EXTEND, TRIM , DELETE..............
What is a PL/SQL Record data type?
Define and declare Records
Different Types of Records - Table-based, Cursor-based, Programmer-defined
Benefits of using Records
Guidelines for using Records...........
What are Cursors?
Define cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN
What are cursor variables? Explain with an example
Significance of SELECT FOR UPDATE clause. Write syntax
Significance of WHERE CURRENT OF clause. Write syntax.........
Overview of PL/SQL Error Handling
Advantages of PL/SQL Exceptions
Explain some of the commonly used Predefined PL/SQL Exceptions.
What is user-defined exception? Explain it with an example.
Explain how PL/SQL exceptions are raised.........
What are PL/SQL Subprograms?
Advantages of PL/SQL Subprograms.
PL/SQL has two types of subprograms called procedures and functions. Explain
How does subprogram pass information? What are actual and formal subprogram
parameters? Explain with an example
Define three parameter modes, IN (the default), OUT, and IN OUT that can be
used with any subprogram............
Explain the purpose of tuning oracle database.
Could you explain the areas where tuning of database is required?
What are the tools provided by oracle to assist performace tuning? Explain them
What is cost based optimization? When is it triggered?
Explain the factor that can cause the execution plan of a query to change
What is index monitoring feature in oracle? Explain how to use it for index
What is replication? Explain its purpose.
Oracle Server supports two different forms of replication: basic and advanced
replication. Explain difference between these.
What is the difference between a snapshot and a materialized view?
Describe how to implement basic snapshot replication.
What object types can and cannot be replicated in oracle?
Describe how to monitor replication.............
What is the Oracle HTTP Server? How does it work?
Explain how to start and stop the Oracle HTTP Server.
Explain how to publish static HTML pages on the Oracle HTTP Server.
Explain how to execute standard CGI-BIN programs from the Oracle HTTP Server.
Explain how to password protect certain directories...........
What is import/export? Explain its usage.
Explain how to use the import/export utilities.
Can we export a subset of a table? Explain how
Can we monitor how fast a table is imported? How?
Explain how to import tables to a different tablespace?
Explain how to improve Import/Export performance?
Describe the common Import/Export problems...........
What is ODBC? Explain its purposes
Difference between ODBC and Net8.
What do I need to get ODBC to work?
Explain how to create a Data Source.
Explain how to trace ODBC calls.
What is ODBC SQLPASSTHROUGH option?
How to attach an Oracle table in MS-Access?
Explain how to get Oracle data into MS-Excel...........
How compatible is Oracle Lite with the standard oracle server?
What is personal oracle lite?
Explain how to connect to a POL database
Explain how to write Java Stored Procedures
Explain how to replicate data using Oracle Lite.............
Why and when should I backup my database?
What strategies are available for backing-up an Oracle database?
Difference between online and offline backups.
Difference between restoring and recovering
Explain how to backup a database using the export utility
Explain how to put a database into ARCHIVELOG mode
Explain how to do off-line database backups..........
Explain how to create a new database
Explain how to rename a database
Explain how to resize tablespaces and data files
Explain how to find the overall database size
Explain how to find used/free space in a TEMPORARY tablespace
What is ORADEBUG? Explain its uses?
Explain how to dump internal database structures...........
What is Oracle Parallel Query?
Difference between Parallel Query and Parallel Server
Explain how to invoke Parallel Query
Explain how to disable Parallel Query
Can single-processor systems benefit from Parallel Query? Explain..........
What is RAC and how is it different from non RAC databases?
Can any application be deployed on RAC? Explain
Explain how to convert a single instance database to RAC
How do we stop and start RAC instances?
Can we test if a database is running in RAC mode? How?
How can we keep track of active instances?.........
What is a PeopleSoft database?
How does the PeopleSoft database interact with the Oracle database?
What is the order of installing PeopleSoft on Oracle?
How can the client workstation connect to a PeopleSoft database?
What is the sizing of a generic PeopleSoft HRMS database?..........
Latest answer: A JOIN is used to match/equate different fields
from 2 or more tables using primary/foreign keys. Output is based on type of
Join and what is tobe queries...................... Read
Latest answer: Collections are usually referred to as Composite
Data types. Composite type is one that has components in it. A variable of a
composite type contains one or more scalar types................ Read answer
Latest answer: CHAR and NCHAR are character data types which
and have a fixed length. However, CHAR has a specified size in bytes by default
and NCHAR has a size specified in characters by default................ Read
Latest answer: The BFILE datatype is used to store unstructured
binary data outside the database. The column of BFILE type stores file locator
that points the OS file which actually stores data........... Read answer
Latest answer: Oracle engine uses private working area to
process queries. This work area is called as cursor. The data that is stored in
the cursor is called as Active Data Set............. Read
Latest answer: A cursor variable is capable to get associated
with different SELECT statements at run time. It is a reference type which is
quite similar to pointer in C. In order to use cursor variable........... Read answer
Latest answer: Differentiate between Data Mining and Data
warehousing. What is Data purging? What are CUBES? What are OLAP and OLTP? What
are the different problems that “Data mining” can solve? What are different
stages of “Data mining”?..................... .
Latest answer: Explain the concepts and capabilities of OLAP.
Explain the functionality of OLAP. What are MOLAP and ROLAP? Explain the role
of bitmap indexes to solve aggregation problems. Explain the encoding technique
used in bitmaps indexes. What is Binning? What is candidate
What are triggers? What are the events a trigger includes? The procedures are called as triggers in oracle and it run implicitly when an insert, update or delete statement is being performed on a table. They are same as store procedures but it can include SQL, PL/SQL or Java statements. Triggers are implicit to the application. It includes the following events such as: - DML statements for modification of data like INSERT, UPDATE or DELETE - DDL statements - System related messages like startup, shutdown, and error messages - User events like logon or logoff
Rohit Sharma 12-7-2011 07:12 AM
Oracle interview questions and answers
Does the After report trigger get fired if report execution fails? Yes.
Explain PGA. PGA - Program Global Area - area in memory used by a single Oracle user process.
Ramakutty 12-6-2011 04:09 AM
Oracle interview questions and answers
List various levels of Auditing ? - Statement Auditing, - Privilege Auditing - Object Auditing.
List the types of snapshots. - Simple - Complex
Ankur 12-6-2011 04:08 AM
Oracle interview questions and answers
List the characterstics of data files. - Data file can be associated with only one database. - Once it is created, it can't change size. - One or more database files together form a table space.
When does a Transaction finish ? - When it is committed - OR When it is Rolled back.
What is the function of ROLLBACK? - It retracts the changes occuring from the execution of SQL statements in the transaction.
Rajat 12-6-2011 04:08 AM
Oracle interview questions and answers
Explain tablespace. Logical Storage Unit into which a database is divided.
What is Oracle View? Oracle View - A virtual table which has a query attached to it.
Do views contain/store data? No. Views do not contain/ store any data.
A database backup while it is still up and running is a Hot backup and it must
be in archive log mode.
A cold backup is a backup while it is shut down. The database does not require
being in archive log mode in this mode.
The benefit of a hot backup is that the database is still available for use
while the backup is occurring.
A cold backup is easier to administer the backup and recovery process.
Cold backups does not require being in archive log mode and thus slight
performance gain as the database is not writing archive logs to disk.
What are actual and formal parameters?
The variables or expressions referenced in the parameter list of a subprogram
call are actual parameters.
Following procedure call lists two actual parameters named empno and amt:
The variables declared in a subprogram specification and referenced in the
subprogram body are formal parameters.
Following procedure declares two formal parameters named empid and amt:
PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;
Oracle interview questions
Can we protect our PL/SQL source code?
Yes, we can protect. PL/SQL V2.2 implements a binary wrapper for PL/SQL programs
to protect the source code.
The utility available in this version transforms the PL/SQL source code into
portable binary object code and thus protect from exposing your proprietary
algorithms and methods.
Explain an Exception and its types.
Exception is the error handling part of PL/SQL block.
Exception is of two types: Predefined and user defined.
Some of Predefined exceptions are
More oracle interview questions
Oracle Question 1: How to retrieve 5th highest
sal from emp table? Oracle Question 2: What is $FLEX$ and
$PROFILES$? Where are they used? Oracle Question 3: Explain how to pass values
from one table to another by using Bulk collect. Oracle Question 4: How to call a trigger
inside a stored procedure? Oracle Question 5: What is WATER MARK IN
oracle? Explain the significance of High water mark. Oracle Question 6: What is an object groups? Oracle Question 7: Difference between
clustering and mirroring Oracle Question 8: Difference between paging
and fragmentation Oracle Question 9: Can you explain how to
insert an image in table in oracle? Oracle Question 10: How to find out second
largest value in the table? Oracle Question 11: Disadvantage of user
defined function in oracle. Oracle Question 12: Explain the significance
of cluster table or non cluster table. Oracle Question 13: What is pragma
restrict_reference in oracle 9i? When do we use it? Oracle Question 14: Why can't we assign not
null constraint as table level constraint in oracle? Oracle Question 15: Explain the use of
between page triggers in REPORTS. Oracle Question 16: How to delete all
duplicate records from a table using subquery? Oracle Question 17: How many types of trigger
can be used in a table at a time? What are they? Oracle Question 18: What is partitioned
table? What are its types? Explain its purpose and how to create. Oracle Question 19: What is the role of
Archiver [ARCn]. Oracle Question 20: Structural difference
between bitmap and btree index in oracle. Oracle Question 21: Can you explain how to
convert oracle table data into excel sheet? Oracle Question 22: When should we go for
hash partitioning? Oracle Question 23: What is Materialized
view? What is a snapshot? Oracle Question 24: What are the advantages
of running a database in NO archive log mode? Oracle Question 25: What are the advantages
of running a database in archive log mode? Oracle Question 26: What is dba_segment in
oracle? Oracle Question 27: Purpose of using Nextval
while creating the sequence. Oracle Question 28: What is forall Statement?
Explain with an example Oracle Question 29: What is flashback Query?
Explain its uses with an example Oracle Question 30: What is the tablespace in
Oracle? Purpose and significance Oracle Question 31: How to use sequence and
what use of sequence cache? Oracle Question 32: Difference between
formula column and place holder.
Oracle Question 33: How to create placeholder
columns in oracle? Oracle Question 34: What is autonomous
transaction? Oracle Question 35: How to create LOV in
Oracle forms? Oracle Question 36: Which sql command to be
used to get a print out from oracle?
Oracle Question 37: What is sql*loader
parameters and where do we use it? Oracle Question 38: Explain how to see the
report output in excel sheet in oracle applications.
Oracle Question 39: Can we restore a Table
that accidentally dropped? How? Oracle Question 40: What is the use of Data
Link in Reports? Oracle Question 41: What are the types of
triggers available in Oracle Reports?
Oracle Question 42: What are the triggers
associated with image items? Explain them Oracle Question 43: Explain how to view the
status of the Rollback segment in oracle. Oracle Question 44: What is the use of
NOARCHIEVELOG parameter in oracle database?
Oracle Question 45: How the SMON process is
used to write into LOG files?
Oracle Question 46: What are the types of calculated columns
available? Explain them
To retrieve data from an Oracle database using ODP.NET, we need to work with a
few of the ODP.NET classes.....
Retrieving Data Using OracleDataReader
OracleDataReader is simply a read-only and forward-only result set. It works
only if the database connection is open and it makes sure that the connection
is open while you are retrieving data. As the data that it retrieves is
read-only, it is a bit faster than any other method to retrieve data from
Working with Data Tables and Data Sets
The OracleDataAdapter class is mainly used to populate data sets or data tables
for offline use. The OracleDataAdapter simply connects to the database,
retrieves the information, populates that information into datasets or data
tables, and finally disconnects the connection to the database.......
DataTableReader is complementary to a DataTable object, and is mainly used as a
type of Data Reader in the disconnected mode................
Presenting Master-Detail Information Using a Dataset
A DataSet object can have its own relations between data tables existing
in it. We can add these relations dynamically at the client side (within an
application), to represent master-detail (or hierarchical) information......
the OracleCommand Object
OracleCommand is not simply meant for OracleDataReader. It has got a lot of
functionality for itself. Let us see few of the most commonly used features of
OracleCommand in this section.......