Tech Mahindra Oracle DBA Interview Questions - 1

Question: 1

What is a lock?

Lock is a mechanism provided by Oracle to reserve a database object so that different sessions do not interfere in each other’s work.

Locking helps in ensuring data consistency and maintaining database objects in usable state in a multi user environment. However, it can cause one session to block another.

Question: 2

What is a latch? How it is used in Oracle?

A latch is a semaphore or an on/off switch in Oracle database that a process must access in order to conduct certain type of activities. Latches govern the usage of Oracle’s internal resources by its processes.

They enforce serial access to the resources and limit the amount of time for which a single process can use a resource.

There are over 80 latches available in Oracle.

Question: 3

What are the background trace files?

Background trace files are associated with background processes and are generated when certain background process experiences an error.

The information in background trace files is generally used for trouble shooting.

Question: 4

What is the difference between latches and enqueues?

Enqueue is used to queue request for lock on any db object that can’t be served immediately and session is ready to wait.

Latches are internal locking mechanism of Oracle to provide short term exclusive access to Oracle’s internal objects like library cache etc.

Another difference is that enqueues follow first in first out (FIFO) algorithm while latches do not follow any such algorithm.

Question: 5

How would you determine what sessions are connected and what resources they are waiting for?

You can use the V$SESSION and V$SESSION_WAIT dynamic performance views.