Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 1 August 2013

DBA Interview Questions with Answers Part19

Why we look for CHUNKS_FREE space while tracking fragmentation details query?
The CHUNK_FREE return the number of chunks of contiguous free space based on dba_free_space table. The motive is to find the largest size chunks of free space within a tableapce. This is because as we know oracle server allocates space for segments in unit of one extent. When the existing extent of segment is full, the server allocates another extent for the segment.
In order to do oracle searches free space in the tablespace (contiguous set of data block sufficient to meet the required extent). If sufficient space not found then an error is returned by the oracle server.
What is the impact of NLS/Characterset in database?
NLS is a National language support and encompasses how to display currency, whenever we use a comma or a dot to separate numbers, how the name of the day is spelled etc.
Charactersets are how we store data. 
For Example: US7ASCII is a 7bit characterset and WE8ISO8859P1 8 bit character set. It can store 2 times as many characters as the 7bit characterset. If you try to export from 8 bit characterset database and import into 7bit database then there is chance to loose data in 7bit characterset that have the high bit set and if you try from 7bit to 8bit would not encounter any issues since the 7bit characterset is a subset of the 8bit characterset and can hold more types of characters and can support many countries.
Can we perform RMAN level 1 backup without level 0?
If no level 0 is available, then the behavior depends upon the compatibility mode setting (oracle version). If the compatibility mode less than 10.0.0, RMAN generates a level 0 backup of files contents at the time of backup. If compatibility is greater than 10.0.0 RMAN copies all block changes since the file was created, and stores the results as level 1 backup.
What will happen if ARCHIVE process cannot copy an archive redolog to a mandatory archive log destination?
Oracle will continue with cycle to the other online redolog groups until it return to the group that the ARCH process is trying to copy to the mandatory archive log destination. If the mandatory archive log destination copy has not occurred, the database operation will suspend until the copy is successful or the DBA has intervened to perform force log switching.
Can you differentiate between HOTBACKUP and RMAN backup?
For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. In fact RMAN has a number of advantages over general backup. For more information please check: Benefit of RMAN Backup
How to put Manual/User managed backup in RMAN?
In case of recovery catalog, you can put by using catalog command:
RMAN> CATALOG START WITH ‘/oraback/backup.ctl’;
When you put any SQL statement how oracle responds them internally?
First it will check the syntax and semantics in library cache, after that it will created execution plan. If already data in buffer cache (in case of identical query) it will directly return to the client. If not it write the fetch to the database buffer cache after that it will send server and finally server send to the client.
Can we use Same target database as Catalog?
No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted state.
Differentiate the use of what are PGA and UGA?
When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).
How do you automatically force the oracle to perform a checkpoint?
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 is Cluster table in Oracle database?
A Cluster is a schema object that contains one or more tables that all have one or more common columns. Rows of one or more tables that share the same value in these common columns are physically stored together within the database. Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT and UPDATE statements that modify cluster key values.
Can you differentiate between complete and incomplete recovery?
An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup.
What is difference between RMAN and Traditional Backup?
RMAN is faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode. Check: Benefit of RMAN Backup
What are bind variables and why are they important?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.
How to recover database without backup?
If flash recovery is enabled then we can recover database without having backup? Otherwise we cannot recover database without backup.
How to write explicit cursor to avoid oracle exception: no_data_found and too_many_rows?
In PL/SQL if you try to write select statement with into clause it may return two exception no_data_found and too_many_rows to avoid this exception you have to write explicit cursor.
Exception Block,
When no_data_found
// Put your code
// put your code
When others then
// put your code
What are differences between Reference cursor and Normal cursor?
Reference cursor gives the address of the location instead of putting item directly. It holds the different type of structures. Normal cursor holds one structure of table.
Reference cursor is a dynamic cursor where as normal cursor is static cursor. In dynamic cursor single statement are process multiple select statement dynamically at run time where as in normal cursor we process only one select statement.
What is Pipeline view?
In case of normal views whenever you call the view it will get data from the base table where as in case of pipeline view if you call the view it will get data from another intermediate view.
How would you find the performance issue of SQL queries?
– Enable the trace file before running your queries
– Then check the trace file using tkprof create output file.
– According to explain plan check the elapsed time for each query
– Then tune them respectively.
What is difference between Recovery and Restoring of database?
Restoring means copying the database object from the backup media to the destination where actually it is required where as recovery means to apply the database object copied earlier (roll forward) in order to bring the database into consistent state.
What are the Jobs of SMON and PMON processes?
SMON – System Monitor performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.
PMON – Process Monitor failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.
When you should rebuild index?
In fact in 90% case never. When the data in index is sparse (lot of holes in index, due to delete and updates) and your query is usually ranged based. Also index BLEVEL is one of the key indicators of performance of SQL queries doing index range scan.
What is key preserved table?
A table is set to be key preserved table if every key of the table can also be the key of the result of the join. It guarantees to return only one copy of each row from the base table.
Which of the following is NOT an oracle supported trigger?
  1. Before
  2. During
  3. After
  4. Instead of
Answer: B
Which of the following is NOT true about modifying table column?
  1. You can drop a column at any time.
  2. You can add a column at any time as long as it is a NULL column.
  3. You can increase the number of characters in character columns or number of digits in numeric columns.
  4. You can not increase or decrease the number of decimal places.
Answer: D
How can you find SQL of the Currently Active Sessions?
Compare tables V$SQL view by SQL_address with V$SESSION view of currently active sessions
If you have ASM database that used by different production systems immediately shutdown then what happens to the production system?
In that case the other database would need to shutdown abort.
How do you move table from one tablespace to another tablespace?
You can use any of the below method to do this:
1.      Export the table, drop the table, create definition of table in new tablespace and then import the data using (imp ignore=y).
2.      Create new table in new tablespace then drop the original table and rename temporary table with original table name.
CREATE TABLE temp_name TABLESPACE new_tablespace as select * from ‘source_table’;
DROP TABLE real_table;
RENAME temp_name to real_table;
For More Interview Preparation Click on Link:


  1. DreamHost is the best hosting provider for any hosting services you might require.