30 Awesome Frequently asked DBA questions

I am here presenting some of the most commonly asked DBA questions. We have provided the required answer to these DBA questions also

Question 1) How to find Database version ?

Answer SQL> select * from v$version;

The command returns the release information,

Question 2) How to find opatch Version ?

Answer opatch is utility to apply database patch , In order to find opatch version execute”$ORACLE_HOME/OPatch/opatch version”
Even if you simple write $ORACLE_HOME/OPatch/opatch, it will show the opatch version in the start

Question 3) How to find out invalid objects in the apps schema in the database?

Answer select count(*) from dba_objects where status =’INVALID’and owner =’APPS’
Question 4) How you will see hidden files in linux/solaris?

Answer ls -la
Question 5) How to find that the database is 64-bit/32-bit?

Answer $RDBMS_ORACLE_HOME/bin/file oracle
Question 6) What is top command?

Answer top is a operating system command, it will display top 10 processes which are taking high cpu and memory. 8. What is a patch?Answer A patch can be a solution for a bug/it can be a new feature.
Question 7) Which table u will query to check the tablespace space issues?

Answer bytes column in dba_free_spaces and dba_data_files
Question 8) Which table u will query to check the temp tablespace space issues?

Answer dba_temp_files

Question 9) What is temp tablespace?

Answer Temp tablespace is used by so many application programs for sorting and other stuff.

Question 10) How to find the locks and what is the resolution?

Answer we can find general locks with the following query:

SELECT inst_id,DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,  id1, id2, lmode, request, type  FROM GV$LOCK  WHERE (id1, id2, type) IN  (SELECT id1, id2, type FROM gV$LOCK WHERE request>0)ORDER BY id1, request ;

If it’s a dead lock, we need to kill that session.

Related :Enqueue and Locks
Question 11) How to kill a database session?

Answer alter system kill session ‘&sid,&sno’;

Question 12) How to apply a rdbms patch?

Answer Using opatch

Go the patch directory

cd <patch num>

opatch apply

what is PSU patch and how to verify the PSU update in the Oracle Home

Question 13) How to find opatch is enabled or not for u r database?

Answer If Opatch directory exists under RDBMS_ORACLE_HOME.

Question 14) What is the pre-req for applying a rdbms patch?

Answer Inventory should be set in file oraInst.loc @/var/opt/oracle or /etc and Oracle home must be registered in Central inventory

Question 15). What is Inventory?


The oraInventory is the location for the OUI (Oracle Universal Installer)’s book keeping. The inventory stores information about: All Oracle software products installed in all ORACLE_HOMES on a machine Other non-Oracle products, such as the Java Runtime Environment (JRE)

In a R12 Application system ,the RDBMS and iAS ORACLE_HOMEs and 10.1.2 are registered in the oraInventory.

How to run Opatch in non interactive form
Question 16). What are different types of inventories?

Answer The Global inventory (or Central inventory)

The Local inventory (or Home inventory)

Question 17). What is Global inventory or central inventory?

Answer The Global Inventory is the part of the XML inventory that contains the high level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc.The Global Inventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs.The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone.

Note: If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global Inventory synchronized.

Question 18). What is local inventory?

Answer There is one Local Inventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME.The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.
Question 19). What is AWR?

Answer AWR is a database utility to gather database and session level performance information.

All about AWR
Question 20) How to enable trace at database level?

Answer set init.ora parameter sql_trace
Question 21) How to enable trace for a session?

Answer  Alter system set sql_trace=true;

Execute the sql query

Alter system set sql_trace=false;

This will create a trace file at background dump dest directory

Question 22) How to enable trace for other session?

Answer exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)

Example To enable trace for sql session with sid 9779

SQL> exec sys.dbms_system.set_sql_trace_in_session(9779,8767896,true);

PL/SQL procedure successfully completed.

To disable trace

SQL> exec sys.dbms_system.set_sql_trace_in_session(9779,8767896,false);
Question 23) .What is the location of init.ora ?


Question 24). What is that trace files contains and the utiliy used to read them?

Answer Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.

Question 25) What is the syntax for tkprof?

Answer tkprof explain=apps/ sys=no

Question 26). What is a database link? How to create it?

Answer If we want to access objects of another database from this database then we need a database link from this database to the other.

1.Login as oracle user

2.sqlplus “/as sysdba”

3. create database link connect to identified by using ”;

create database link MY1_TO_MY2 connect to apps identified by apps using ‘MY2′;

Database link created.

SQL> select name from v$database@ MY1_TO_MY2;




SQL>select db_link from dba_db_links;
Add destination database tns entry in tnsnames.ora

Question 27) How to do database clone?

Answer Read the below link

How to clone the database using manual hot backup

How to clone the database using manual cold backup
Question 28) How to find the database/sqlplus version?

Answer select banner from v$version;
Question 29) How to find operating system version?

Answer uname –a

Question 30) . While applying a rdbms patch using opatch you are getting the error, unable to read inventory/inventory is corrupted/ORACLE_HOME is not not registered, what you will do, and how you will apply the patch?

Anwer We will check the inventory directory permission, try to apply the patch after giving 777 permissions to that inventory directory.

We can try to create to recreate the central inventory and If still it won’t work we will apply patch with the following command:

Opatch apply no_inventory

Hope you like these post on DBA questions  .Please do provide the feedback