DBA Interview Questions and Answerss

When a query is sent to the database and an index is not being used, what type of execution is taking place?

Full Table Scan(FTS)

How do you measure table fragmentation?

To find the table level fragmentation:The first thing we have to do is, we have to analyze the table by using ANALYZE TABLE COMPUTE STATISTICS; After that we have to check thetable name called dba_tables column called chain_cnt if the value of chain_cnt>0 the table is fragmented.keep in mind that we came to know this only after analyzing the particular table by using ANALYZE.. cmd. before go to the analyzing we check when the corresponding table has analyzed by the table dba_tables column is last_analyzed.


Difference between lock and latches?


locks are used to protect the data or resourses from the simulteneous use of them by multiple sessions which might set them in inconsistant state... Locks are external mechanism, means user can also set locks on objects by using various oracle statementsWhile latches are for the same purpose but works at internal level. Latches are used to Protect and control access to internal data structres like various SGA buffers.They are handled and maintained by oracle and we can't access or set it.. this is the main difference


why do we switch from pfile to spfile and when do we use spfile ?

We switch from pfile to spfile when we want to change some parameters in init.ora dynamically (ie. when the database is up and running ) .In case of pfile if we change the value of some parameters while the db is running , it will take effect on next time the db starts .

what is checkpoint?

At checkpoint the background process CKPT does the following1)signalling the DBWR(Background process) at checkpoints2)Updating the datafile headers with checkpoint information3)Updating controlfiles with checkpoint information.


What is the difference between TRUNCATE and DELETE?

The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

What is written in Redo Log Files?

redolog gets written by the background process lgwr in the following circumfstances1. at commits2. redo log bufer becomes 1/3 full3. when there is more thana 1Mb of changed records in the redo log bufer4. when a timeout occurs (every 3 seconds)5. before the DBWn writes modified blocks in the databse buffer cache to the data files

How many maximum Redo Logfiles one can have in a Database?

Maximum number of log files a database can accomodate depends on the parameter "MAXLOGMEMBERS" specificed during database creation.