This article contains the most commonly asked top 40 oracle dba interview questions and answers
1) What is oracle database ?
Answer: Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other
Answer:Oracle database is the collection of datafiles,redologs and controlfiles while Oracle instance is the SGA ,processes in the Memory.
We can have 1 or more instance serving a oracle database . In RAC, we have one set of datafiles,controlfile and redo logs while instance on one ore more boxes accesses the same database
3)What is a Tablespace?
Answer: Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema’s and a schema can have multiple tablespace’s. Database creates “SYSTEM tablespace” by default during database creation. It contains read only data dictionary tables which contains the information about the database.
All you need to know about oracle create tablespace statement
4) What are Datafiles?
Answer:The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.
5) what is Control Files?
Answer:Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database such as Database name and the Names and locations of datafiles and redo log files.
6) What is Redo Log Files?
Answer The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.
7) What is Archive Log Files ?
Answer:Oracle automatically archives log files when the database is in ARCHIVELOG mode. This prevents oracle from overwriting the redo log files before they have been safely archived to another location.
8) What is Parameter Files (initSID.ora)
Answer:Parameter files contain a list of configuration parameters for that instance and database.
9) What is schema?
Answer:A user account and its associated data including tables, views, indexes, clusters, sequences,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc are default schema’s. We can create a new Schema/User. But we can’t drop default database schema’s.
Oracle Create User , System Privileges and Oracle Object Privileges
10) What is data blocks ?
Answer:Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk.The datablocks can be 4K,8 K size depending on the requirement.
11) What is an Extent ?
Answer:Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.
12) What is a Segment ?
Answer:A segment is a collection of extends which is used for storing a specific data structure and resides in the same tablespace.
13) What is Rollback Segment ?
Answer:Database contain one or more Rollback Segments to roll back transactions and data recovery.
14) What are the different type of Segments ?
Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment.
15) What is archivelog and No archive log mode?
Answer: We all know that redo logs stored the redo information and redo log files are in circular fashion.Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. No archive log means archive log are not generted and redo are overwritten
16) What all things are present in the shared pool?
Answer: The shared pool portion of the SGA contains three major areas:
library cache(contains parsed sql statements,cursor information,execution plans)
dictionary cache (contains cache -user account information,priveleges information,datafile,segment and extent information)
buffers for parallel execution messages
17) What is hotbackup?
Answer:If the database must be up and running 24 hours a day, seven days a week, then you have no choice but to perform inconsistent backups of the whole database. A backup of online data files is called an online backup. This requires that you run your database in ARCHIVELOG mode.
18) which views is used to finding the locking in the database?
Answer: v$lock, v$session, v$process
Oracle Enqueue and locks(Oracle table locks): How it works
19) You have many instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
There are two ways
|SQL> oradebug setmypid
SQL> oradebug ipc
Another way is to use
20) What is Database index ?
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.By default, Oracle creates B-tree indexes.
Oracle Indexes and types of indexes in oracle with example
21) What is difference between the hotbackup taken through RMAN and Manual?
Answer check the below link for Detailed explanation
22) What is library cache lock?
A library cache lock means that a session is waiting to use or change an object definition or to use a SQL statement that another session is loading, changing or parsing, or is waiting to change or parse. This usually indicates that database object definitions are being changed regularly. Another example is gathering statistics on an object. When statistics are gathered all references to that object in the shared pool or library cache become invalid, requiring a new hard parse for each SQL statement referencing the object. Statistics should only be gathered when there are no active users or system activity is very low.
23)How do we find the blocker for Library cache lock?
We can run hanganalyze to find the blocking session.
Many times the below query also works in wonderfull manner
|select /*+ all_rows */ w1.sid waiting_session,
decode(h.kgllkmod, 0, ‘None’, 1, ‘Null’, 2, ‘Share’, 3, ‘Exclusive’,
decode(w.kgllkreq, 0, ‘None’, 1, ‘Null’, 2, ‘Share’, 3, ‘Exclusive’,
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
24) How to take global hanganalyze dump?
ORADEBUG setinst all
ORADEBUG -g def hanganalyze <level>
25) How do you recover the database if you lost one of the controlfile in the database?
Check below link for all the useful scenerio for recovery
26) How do you recover the database if you lost all of the controlfile in the database?
Check below link for all the useful scenerio for recovery
27) What is flashback database?
28) How can you rebuild an index?
Answer: We can rebuild the index using the below command
alter index index_name rebuild online; If it is to be online
alter index index_name rebuild; If it is to be rebuild offline
29) What is Branch Block in index ?
Answer: Branch block rows hold <separator key,kdba> pairs used to guide the B-tree search
to a row in a leaf block.
30) What is Leaf Block in index ?
Answer: Leaf block rows hold the <KEY, KEYDATA> pairs stored by the B-tree.
31) What is High Water Mark in Oracle?
1. High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.
2. Delete Table operation won’t reset HWM.
3. TRUNCATE will reset HWM.
4. The high water mark level is just a line separate the used blocks and free blocks.
The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.
32) What parameters are used to set parallelism in the database?
Answer:Following initialization parameters are required for parallelism setup in database.
33) If the table is fragmented, how would you rebuild it?
First we need to rebuild the table
alter table <table_name> move;
Secondly we need to rebuild all its indexes
We can find all the indexes
select index_name from dba_indexes where table_name=<table name> and table_owner=<table owner>
alter index <index_name> rebuild
Finally we should gather the stats on the table
How to rebuild the table in oracle
34) What view would you use to determine free space in a tablespace?
35) How do you switch from an init.ora file to a spfile?
Create spfile from pfile;
It will start using spfile
36) You are experiencing high “busy buffer waits” . how can you find what’s causing it?
Answer Buffer busy wait means that the queries are waiting for the blocks to be read into the db cache.There could be the reason when the block may be busy in the cache and session is waiting for it. It could be undo, data block or segment header wait.
Run the following query to find out the p1,p2 and p3 of a session causing buffer busy wait
|sql> select p1 “File #”,p2 “Block #”,p3 “Reason Code” from v$session_wait where event = ‘buffer busy waits’;|
After that running the following query to find the segment causing buffer busy wait:-
|sql> select owner,segment_name,segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1|
37) How to kill the database session?
|alter system kill session ‘SID,SERIAL#’|
38) What is difference between startup mount and startup nomount?
Answer. startup mount -mount the control file
startup nomount- does not mount the controlfile
39)How to check Oracle database version?
|Select * from v$version;|
40) What is SCN (System Change Number) ?
Answer The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.
Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file
Hope you like this compilation of oracle dba interview questions and answers.This will be helpful to senior DBA also. Please do provide the feedback