:: Frequently Asked Interview Questions for 2+ Candidate ::
Experience
Level : 2 -3 years
What is main purpose of CHECKPOINT?
A Checkpoint is a database event, which synchronizes the data
blocks in memory with the datafiles on disk.
A checkpoint has two purposes:
1. to establish data consistency
2. Enable faster database recovery
The following are the parameter that will be used by DBA to adjust
time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600; //
Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; //number
of OS blocks.
What does RESETLOGS option do?
1. Creates a new incarnation of the database, putting a new SCN in
all data file headers.
2. Reset Log Sequence number to 1
3. Reformats ONLINE REDO LOGFILES if they exists
In what scenarios open resetlogs required ?
An ALTER DATABASE OPEN RESETLOGS statement is required,
1. after incomplete recovery (Point in Time Recovery) or
2. recovery with a backup control file.
3. recovery with a control file recreated with the reset logs
option.
What to do if my Global Inventory is corrupted ?
No need to worry if your global Inventory is corrupted, you can
recreate global Inventory on machine using Universal Installer and attach
already Installed oracle home by option
-attachHome
./runInstaller -silent -attachHome -invPtrLoc
$location_to_oraInst.loc
ORACLE_HOME=”Oracle_Home_Location”
ORACLE_HOME_NAME=”Oracle_Home_Name”
CLUSTER_NODES=”{}”
Oracle version 11.2.0.4.0 what does each number refers to?
Oracle version number refers :
11 – for Major database release number
2 – for Database Maintenance release number
0 – for Application server release number
4 – for Component Specific release number
0 – for Platform specific release number
What is Row chaining and how can you find it? and how you will
resolve the issue ?
When you are inserting record if a row is not fit into the single
block… it will chain across multiple blocks leaving a pointer between these blocks.
Find using below query
SQL> analyze table <owner>.<table_name>;
SQL> select table_name,chain_cnt from dba_tables where
table_name=’tablename';
To Overcome :
create a table with bigger the block size
1) Create tablespace tbs1 data file ‘/u01/oradata/test/data01.dbf’
size 100m blocksize 16k;
2) alter table tablename move to tbs1;
Here tbs1 is the tablespace name with larger block size and before creating tablespace it is assumed that
you have created a db buffer cache for it.
What is row migration? When does it occur? Where can you find this
information?
Row migration happens when update occurs at one column and the row
is not adequate to fit in the block then the entire row will be moved to the
new block.
Find using below query
SQL> select table_name,chain_cnt from dba_tables where
table_name=’tablename';
To Overcome :
-set pct_free storage parameter for table to adequate value
Do you know about statistics , what is the use of it? What kind of
statistics exists in database?
Statistics is a collection information about data or database
There are different types of statistics that oracle maintains-
1)System-Statistics: statistics about the hardware like cpu
speed,I/O speed,read time write time etc : select * from aux_stats$
2)Object statistics : For a table oracle collects the information
about no.of rows,no.of blocks,avg row length etc.We can view
SQL>select table_name,num_rows,blocks,avg_row_len from
dba_tables
for index oracle collect statistics on index column about no.of
rows,no.of root blocks,no.of branch blocks,no.of leaf blocks,no.of distinct
values etc.
