Oracle DBA Interview Questions

/ Interview Questions / By

Important Oracle DBA interview questions

1. Explain the steps involved in executing a data manipulation language (DML) statements?

  1. The server process receives the statement and checks the library cache for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, the server process checks the user’s access privileges for the requested data, and the existing shared SQL area is used to process the statement. If not, a new shared SQL area is allocated for the statement, so that it can be parsed and processed.
  2. If the data and undo segment blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache. The server process locks the rows that are to be modified.
  3. The server process records the changes to be made to the data buffers as well as the undo changes. These changes are written to the redo log buffer before the in-memory data and undo buffers are modified. This is called write-ahead logging.
  4. The undo segment buffers contain values of the data before it is modified. The undo buffers are used to store the before image of the data so that the DML statements can be rolled back, if necessary. The data buffers record the new values of the data.
  5. The user gets feedback from the DML operation (such as how many rows were affected by the operation).

2.  Explain the steps involved in executing COMMIT command ?

  1. The server process places a commit record, along with the system change number (SCN), in the redo log buffer. The SCN is monotonically incremented and is unique within the database.
  2. The LGWR background process performs a contiguous write of all the redo log buffer entries up to and including the commit record to the redo log files.
  3. If modified blocks are still in the SGA, and if no other session is modifying them, then the database removes lock-related transaction information from the blocks.
  4. The server process provides feedback to the user process about the completion of the transaction.

3. What is SGA? What are the three main components of SGA?

The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance.

The main components of SGA are

  • Database Buffer cache
  • Redo Buffer cache
  • Shared pool

The shared pool is further divided into two sections such as Data Dictionary Cache and Library Cache.

4. Explain briefly about SMON, PMON, DBWn,LGWR and CKPT?

Please click here learn about the background processes.

5. What is oraInventory ?What is the Difference Between Local Inventory and Global Inventory?

oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.

There are basically two kind of inventories,

One is Local Inventory (also called as Oracle Home Inventory) and other is Global Inventory (also called as Central Inventory).

Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories)
You will see entry like
HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/

Local Inventory : Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.
What is Oracle Home Inventory?
Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location:
$ORACLE_HOME/inventory
It contains the following files and folders:
· Components File
· Home Properties File
· Other Folders

 

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.