Oracle Interview Questions and Answers
What is the use of ROWS option in EXP command ?
Flag to indicate whether table rows should be exported.
If 'N' only DDL statements for the database objects will
What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress
fragmented segments into single extents.
How will you swap objects into a different table space
for an existing database ?
Export the user
Perform import using the command imp system/manager
This will create all definitions into newfile.sql. Drop
Run the script newfile.sql after altering the
Import from the backup for the necessary objects.
How does Space allocation table place within a block ?
Each block contains entries as follows
Fixed block header
Variable block header
Row Header,row date (multiple rows may exists)
PCTEREE (% of free space for row updation in future)
What are the factors causing the reparsing of SQL
statements in SGA?
Due to insufficient Shared SQL pool size. Monitor the
ratio of the reloads takes place while executing SQL
statements. If the ratio is greater than 1 then increase
the SHARED_POOL_SIZE. LOGICAL & PHYSICAL ARCHITECTURE OF
What is dictionary cache ?
Dictionary cache is information about the databse
objects stored in a data dictionary table.
What is a Control file ?
Database overall physical architecture is maintained in
a file called control file. It will be used to maintain
internal consistency and guide recovery operations.
Multiple copies of control files are advisable.
What is Database Buffers ?
Database buffers are cache in the SGA used to hold the
data blocks that are read from the data segments in the
database such as tables, indexes and clusters
DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.
How will you create multiple rollback segments in a
Create a database which implicitly creates a SYSTEM
Rollback Segment in a SYSTEM tablespace. Create a Second
Rollback Segment name R0 in the SYSTEM tablespace. Make
new rollback segment available (After shutdown, modify
init.ora file and Start database) Create other
tablespaces (RBS) for rollback segments. Deactivate
Rollback Segment R0 and activate the newly created
What is cold backup? What are the elements of it?
Cold backup is taking backup of all physical files after
normal shutdown of database. We need to take.
- All Data files.
- All Control files.
- All on-line redo log files.
- The init.ora file (Optional)
What is meant by redo log buffer ?
Changes made to entries are written to the on-line redo
log files. So that they can be used in roll forward
operations during database recoveries. Before writing
them into the redo log files, they will first brought to
redo log buffers in SGA and LGWR will write into files
frequently. LOG_BUFFER parameter will decide the size.
How will you estimate the space required by a
Calculate the total header size
Calculate the available dataspace per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a
Calculate the number of blocks and bytes required for
After arriving the calculation, add 10 % additional
space to calculate the initial extent size for a working
How will you monitor the space allocation ?
By querying DBA_SEGMENT table/view.
What is meant by free extent ?
A free extent is a collection of continuous free blocks
in tablespace. When a segment is dropped its extents are
reallocated and are marked as free.
What is the use of IGNORE option in IMP command ?
A flag to indicate whether the import should ignore
errors encounter when issuing CREATE commands.
What is the use of ANALYSE ( Ver 7) option in EXP
A flag to indicate whether statistical information about
the exported objects should be written to export dump
What is the use of ROWS option in IMP command ?
A flag to indicate whether rows should be imported. If
this is set to 'N' then only DDL for database objects
will be executed.
What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be
What is the use of INDEXES option in IMP command ?
A flag to indicate whether import should import index on
tables or not.
What is the use of GRANT option in EXP command?
A flag to indicate whether grants on databse objects
will be exported or not. Value is 'Y' or 'N'.
What is the use of GRANT option in IMP command ?
A flag to indicate whether grants on database objects
will be imported.
What is the use of FULL option in EXP command ?
A flag to indicate whether full databse export should be
What is the use of SHOW option in IMP command ?
A flag to indicate whether file content should be
displayed or not.
What is the use of CONSTRAINTS option in EXP command ?
A flag to indicate whether constraints on table need to
What is the use of CONSISTENT (Ver 7) option in EXP
A flag to indicate whether a read consistent version of
all the exported objects should be maintained.
What are the different methods of backing up oracle
- Logical Backups
- Cold Backups
- Hot Backups (Archive log)
What is the difference between ON-VALIDATE-FIELD trigger
and a POST-CHANGE trigger ?
When you changes the Existing value to null, the
On-validate field trigger will fire post change trigger
will not fire. At the time of execute-query post-change
trigger will fire, on-validate field trigger will not
When is PRE-QUERY trigger executed ?
When Execute-query or count-query Package procedures are
Page Numbers :