Home > DBA's day, Definitions, Interview, Interview questions, oracle > 3+Years Oracle DBA Interview Questions

3+Years Oracle DBA Interview Questions

In response for a reader of this blog who asked me for some interview questions for a 3+Year experienced DBA, I have written this series of questions that you can use in your interviews. Either if you are the interviewer, or the interviewer. Enjoy!

1. Basic (Every DBA should answer correctly ALL these questions. This knowledge is just basic for a 3+ year experienced DBA)
1.1 Q- Which are the default passwords of SYSTEM/SYS?
1.2 Q- How can you execute a script file in SQLPLUS?
A- To execute a script file in SQLPlus, type @ and then the file name.
1.3 Q- Where can you find official Oracle documentation?
A- tahiti.oracle.com
1.4 Q- What is the address of the Official Oracle Support?
A- metalink.oracle.com or support.oracle.com
1.5 Q- What file will you use to establish Oracle connections from a remote client?
A- tnsnames.ora
1.6 Q- How can you check if the database is accepting connections?
A- lsnrctl status or lsnrctl services
1.7 Q- Which log would you check if a database has a problem?
A- Alert log
1.8 Q- Name three clients to connect with Oracle, for example, SQL Developer:
A- SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an experienced dba should know at least three clients.
1.9 Q- How can you check the structure of a table from sqlplus?
1.10 Q- What command will you start to run the installation of Oracle software on Linux?
A- runInstaller

2. Moderate (Standard knoledge for a daily-work of every DBA. He could fail one or two questions, but not more)
2.1 Q- What should you do if you encounter an ORA-600?
A- Contact Oracle Support
2.2 Q- Explain the differences between PFILE and SPFILE
A- A PFILE is a Static, text file that initialices the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database.
A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions)
2.3 Q- In which Oracle version was Data Pump introduced?
A- Oracle 10g
2.4 Q- Say two examples of DML, two of DCL and two of DDL
2.5 Q- You want to save the output of an Oracle script from sqlplus. How would you do it?
A- spool script_name.txt
select * from your_oracle_operations;
spool off;
2.6 Q- What is the most important requirement in order to use RMAN to make consistent hot backups?
A- Your database has to be in ARCHIVELOG mode.
2.7 Q- Can you connect to a local database without a listener?
A- Yes, you can.
2.8 Q- In which view can you find information about every view and table of oracle dictionary?
2.9 Q- How can you view all the users account in the database?
2.10 Q- In linux, how can we change which databases are started during a reboot?
A- Edit /etc/oratab

3. Advanced (A 3+ year experienced DBA should have enough knowledge to answer these questions. However, depending on the work he has done, he could still fail up to 4 questions)
3.1 Q- When a user process fails, what Oracle background process will clean after it?
3.2 Q- How can you reduce the space of TEMP datafile?
A- Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g a new feature was introduced, and you can shrink the TEMP tablespace.
3.3 Q- How can you view all the current users connected in your database in this moment?
A- SHUTOWN NORMAL = SHUTDOWN : It waits for all sessions to end, without allowing new connections.
SHUTDOWN IMMEDIATE : Rollback current transactions and terminates every session.
SHUTDOWN ABORT : Aborts all the sessions, leaving the database in an inconsistent state. It’s the fastest method, but can lead to database corruption.
3.5 Q- Is it possible to backup your database without the use of an RMAN database to store the catalog?
A- Yes, but the catalog would be stored in the controlfile.
3.6 Q- Which are the main components of Oracle Grid Control?
A- OMR (Oracle Management Repository), OMS (Oracle Management Server) and OMA (Oracle Management Agent).
3.7 Q- What command will you use to navigate through ASM files?
A- asmcmd
3.8 Q- What is the difference between a view and a materialized view?
A- A view is a select that is executed each time an user accesses to it. A materialized view stores the result of this query in memory for faster access purposes.
3.9 Q- Which one is faster: DELETE or TRUNCATE?
3.10 Q- Are passwords in oracle case sensitive?
A- Only since Oracle 11g.

4. RAC (Only intended for RAC-specific DBAs, with varied difficultied questions)
4.1 Q- What is the recommended method to make backups of a RAC environment?
A- RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.
4.2 Q- What command would you use to check the availability of the RAC system?
A- crs_stat -t -v (-t -v are optional)
4.3 Q- What is the minimum number of instances you need to have in order to create a RAC?
A- 1. You can create a RAC with just one server.
4.4 Q- Name two specific RAC background processes
A- RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.
4.5 Q- Can you have many database versions in the same RAC?
A- Yes, but Clusterware version must be greater than the greater database version.
4.6 Q- What was RAC previous name before it was called RAC?
A- OPS: Oracle Parallel Server
4.7 Q- What RAC component is used for communication between instances?
A- Private Interconnect.
4.8 Q- What is the difference between normal views and RAC views?
A- RAC views has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION
4.9 Q- Which command will we use to manage (stop, start…) RAC services in command-line mode?
A- srvctl
4.10 Q- How many alert logs exist in a RAC environment?
A- One for each instance.

5. Master (A 3+ year experienced DBA would probably fail these questions, they are very specifid and specially difficult. Be glad if he’s able to answer some of them)
5.1 Q- How can you difference a usual parameter and an undocumented parameter?
A- Undocumented parameters have the prefix ‘_’. For example, _allow_resetlogs_corruption
5.2 Q- What is BBED?
A- An undocumented Oracle tool used for foresnic purposes. Stans for Block Browser and EDitor.
5.3 Q- The result of the logical comparison (NULL = NULL) will be… And in the case of (NULL != NULL)
A- False in both cases.
5.4 Q- Explain Oracle memory structure
The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures in the computer s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).

The PGA contains data and control information for a single user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database.
5.5 Q- Will RMAN take backups of read-only tablespaces?
A- No
5.6 Q- Will a user be able to modify a table with SELECT only privilege?
A- He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table.
5.7 Q- What Oracle tool will you use to transform datafiles into text files?
A- Trick question: you can’t do that, at least with any Oracle tool. A very experienced DBA should perfectly know this.
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
Why I’m getting this error?
A- The table has a BLOB column.
5.9 Q- What parameter will you use to force the starting of your database with a corrupted resetlog?
5.10 Q- Name the seven types of Oracle tables
A- Heap Organized Tables, Index Organized Tables, Index Clustered Tables, Hash Clustered Tables, Nested Tables, Global Temporary Tables, Object Tables.

  1. Dik Pater
    April 14, 2011 at 6:08 pm


    nice article.


    Dik Pater.
    The Netherlands.

  2. Ragavendiran
    April 27, 2011 at 5:58 am

    Good Stuff- Really worthy


  3. srikanth
    April 29, 2011 at 3:14 am

    thank you …..
    donw good job
    u have any new stuff forwarad to me

    • April 29, 2011 at 10:54 am

      I’ve been really busy lately, will try to write new articles ASAP.

      Thanks for your comments! If you have suggestions of new articles, I listen for you

    • venkat
      August 14, 2011 at 4:33 am

      very nice tips , and one more thing is here , iam trying as a 3+ DBA , so plz if ur having the another articals plz forward to me, d real time senarios also

      thanku very much.

  4. Vinodn
    April 29, 2011 at 10:47 am

    Very useful information! Thanks a lot!!

  5. jagmohan
    May 6, 2011 at 5:33 pm

    thank you
    very interesting and useful info

  6. Tom
    July 7, 2011 at 2:18 pm

    Thank you, very useful

  7. Md.Asif
    July 11, 2011 at 6:05 pm

    thank u very much Mr.mario……,it is really of great help,can u post some more questions and realtime tools plz

    • July 11, 2011 at 6:59 pm

      Sure Md.Asif, I will update the blog to add some new posts about realtime tools

  8. suhail
    July 13, 2011 at 6:25 am

    i need some more question abut performance tuning …

    • Rama
      September 7, 2014 at 7:07 am

      I am also want more questions about perfomance tuning

  9. Mohammed Aslam
    July 24, 2011 at 9:50 am

    very very nice questions

  10. sowfeer
    July 26, 2011 at 5:03 pm

    2.4 Qusetions answer has some fault

    In this DCL means Data control langauge, os it must revoke and grant
    and DDL means Data Defenition language, so tha would be Create, alter,drop etc…

    But your entry is like this..

    Please Let me know i am right or wrong !

  11. Vu
    July 26, 2011 at 8:19 pm

    I think you got this wrong:
    2.4 Q- Say two examples of DML, two of DCL and two of DDL

    It should be:


    Thanks for the list.

    • August 1, 2011 at 10:30 am

      Vu and sowteer: You are right, I made a mistake and misplaced DCL and DDL. It’s already fixed, thank you for your contribution!

      • Gabriele Villanova
        October 4, 2012 at 11:26 pm

        I think you have forgotten in the DCL statement list ROLLBACK, COMMIT and SAVEPOINT.

    • Ratnesh Kumar Gupta
      July 1, 2013 at 10:41 am

      Dear ,
      I think SELECT is not DML command ,It’s belonging to DQL


      • purna m
        January 23, 2016 at 6:06 pm

        select is a DRL command..

  12. August 3, 2011 at 5:57 pm

    Thank you for sharing this tip’s It’s really helpful me for my interview…

    Keep posted more and more.. so that I could able to explore the things in oracle dba..

    Thanks again..


    • August 4, 2011 at 6:14 am

      Contratulations for your successfully interview! I’m glad to have helped you, more tips will come soon

  13. G SElvam
    August 17, 2011 at 3:05 am

    Very good questions and answers…Recenly I attended DBA interview all the HR -Techincal questions are covered here… Thanks

  14. kumar
    August 28, 2011 at 11:23 am

    good in knowledge improvise it a simple overview on database nice

    October 12, 2011 at 6:01 pm

    Thank you, very useful


    • rahul
      September 13, 2012 at 12:00 pm

      hii murali nice to meet you for dba do we need any work experience???

  16. HImanshu sharma
    October 19, 2011 at 12:08 pm

    its very really works please mario if you have an opening for dba as fresher so can you mail me once

  17. October 22, 2011 at 5:52 am

    your Question
    3.2 Q- How can you reduce the space of TEMP datafile?

    i just simple use resize command its successful .
    so why need drop and recreate ?

    (note:- i a victim of your this such simple question and complex answer, i am 100% sure)
    your question can be
    Q- How can you reduce the space of TEMP datafile when it contain used data ?

    SQL*Plus: Release Production on Sat Oct 22 11:37:47 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    SQL> conn sys@stlbas105 as sysdba
    Enter password:

    SQL> select tablespace_name,USER_BYTES/1024/1024/1024 size_gb from dba_temp_file

    —————————— ———-
    TEMP 12.9990234

    SQL> alter database tempfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\STLBAS\TEMP01.DBF’
    resize 10g;

    Database altered.

    SQL> select tablespace_name,USER_BYTES/1024/1024/1024 size_gb from dba_temp_file

    —————————— ———-
    TEMP 9.99902344



    • October 24, 2011 at 9:20 am

      Hi Halim,

      The resize option is only available for TEMPFILES starting in 11g, it’s a new feature. Before 11g, you had to drop and recreate the temp datafile.

      So your answer would be true only in 11g, but if you are working with 10g or earlier, that won’t work.

      Thanks for your contribution, I hope the questions were useful to you ๐Ÿ™‚

    • dony
      February 22, 2012 at 9:35 am

      i want to know that your database release?you can tell me?

    • Shakir
      May 17, 2012 at 3:55 am


  18. Muhammad Arshad Quraishi
    October 23, 2011 at 4:26 pm

    very good help i got for my interviews

  19. akd
    November 9, 2011 at 10:50 pm

    Thanks……….helped me alot for my interview

    • November 10, 2011 at 7:21 am

      I’m glad I could help you, and I hope you get the job ๐Ÿ™‚

  20. madhavi
    November 20, 2011 at 11:35 pm

    nice questions pls send me more questions.thank u.

  21. sreenivas
    December 1, 2011 at 7:31 am

    very use ful questions for dba candidates

  22. December 13, 2011 at 9:36 am

    such a nice collection. keep going on.

  23. January 2, 2012 at 10:59 am

    Please stste more question regarding RMAN

  24. nice
    January 8, 2012 at 8:38 pm

    thnx……..its really very useful

  25. veera
    January 21, 2012 at 9:45 pm

    Nice QA,,,,please send more QA. Veera from Bangalore.

  26. Praveen
    January 24, 2012 at 10:25 am

    Really it helps a lot, I appreciate the questions and answers that was posted by you and please keep it up so that new learners will get to know what escatly is happening with the oracle and all…thank you

  27. Vicky
    February 2, 2012 at 7:50 pm

    Very nice article. Highly appreciated

  28. Kiran Sutar
    February 3, 2012 at 11:32 pm

    Thanks, A lot it really helpful for the interview…

  29. dony
    February 22, 2012 at 9:33 am

    i do a lab,i found that is not right.i do it in the 10g.i found that using resize command its successful .

    3.2 Q- How can you reduce the space of TEMP datafile?
    A- Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g a new feature was introduced, and you can shrink the TEMP tablespace.

    • January 14, 2013 at 5:13 pm

      In Oracle 10g you can resize the datafile, and reduce it if it’s empty (or at least, the part that you want to release isn’t being used).

      However, in Oracle 11g, you can make a shrink like in a regular datafile, so you can use free blocks and reduce them.

      Anyway, I think that both our answers would be correct, if you say and make an explanation in an interview.

  30. sreenu
    February 23, 2012 at 7:27 am

    Manya many thanx, and please send me many more Q&A.. if u know openings in Oracle DBA, plz intimate dba.psrinivas@gmail.com

  31. sudhakarreddy
    February 28, 2012 at 2:38 pm

    which is faster DELETE ,TRUNCATE?
    ANS:DELETE the data temporarly we can rollback those data again so that it deletes from segment/table.
    TRUNCATE: permnetly delete the data from OS blocks.you can’t rollback that data ….so that truncate takes little bit of time than delete..

    • rajendra
      March 18, 2016 at 7:32 am

      Truncante performs better than delete because when you delete the records from the database, database has to perform 2 actions.

      1.delete from the database

      2.write the deleted records into “rollback” segments.

      But incase of “Truncate” the second activity is not required.

  32. March 3, 2012 at 11:52 am


  33. March 20, 2012 at 12:32 pm

    Hi All,

    This is a nice site,

    It would be highly appreciated, if you kindly give some more oracle database related questions , so that , I can bounce back my knowledge level.

    Thanks in advance ,


  34. abdul wajeed
    March 22, 2012 at 6:48 am

    wow it’s cool.i’m so happy about this courage.it helps alot for every one.if u have more q&a then pls mail me

  35. debojyoti
    April 21, 2012 at 11:30 am

    It’s a great blog , just wanted to request , if you give some more sample questions , live situations based, i believe , it would be highly appreaciated .

    Thank you ,


  36. piyush
    April 27, 2012 at 12:47 pm

    good work

  37. Rakesh Monga
    May 30, 2012 at 7:17 am

    Very Excellent article

  38. shrikant suvarnkar
    May 30, 2012 at 12:15 pm

    thanks it is really good
    I am stuck on this question can you please answer my questions
    what is mean by hit ratio in oracle ?

  39. June 29, 2012 at 6:20 am

    excellent article

  40. M.V.Murugesan
    July 5, 2012 at 7:05 am

    this article gives basics of oracle dba. i want more.

  41. Abdul Munthaqueen Mohammed
    July 10, 2012 at 6:28 pm

    Thanks very excellent

  42. vaman
    July 15, 2012 at 7:57 am

    very usefull for dba cader

    July 24, 2012 at 7:23 am

    good questions. thank u so much for helping me out.

  44. Bashir Ahamad
    August 4, 2012 at 1:11 pm

    Excellent !!

  45. September 12, 2012 at 7:21 am

    very nice material………..

  46. October 2, 2012 at 6:08 am

    Thank you very much.

  47. durgarao
    November 1, 2012 at 8:06 am

    really worty

  48. santosh
    November 7, 2012 at 3:45 pm

    very nice dba interview questions but these are very easy one plstry to post some difficult questions

  49. bhaskar
    November 8, 2012 at 11:09 am


    This is Bhaskar, i want answer for one question…that is
    How do know that database has crashed?
    please help…me…any one…

  50. November 18, 2012 at 3:16 pm

    Wow, awesome blog layout! How long have you ever been blogging for?
    you make running a blog glance easy. The overall look of your site is excellent, let alone the content material!

  51. syed
    November 18, 2012 at 5:54 pm

    Thanks for such a valuable share ๐Ÿ™‚

  52. December 4, 2012 at 1:22 pm

    Really a bunch of nice questions which is more likely to be asked while facing DBA interview,i hv been asked the concept of LOCKING related with concurrent transaction..im happy i answered correctly

  53. Jai
    December 5, 2012 at 6:40 am

    Thanks for your info…its really useful to me…………

  54. ajeet
    December 7, 2012 at 10:43 am

    Thanks very good oracle questions .

    December 29, 2012 at 7:20 am

    good questions

  56. ramesh valavala
    December 29, 2012 at 3:53 pm

    nice article…

  57. amar
    January 9, 2013 at 2:55 pm

    Hi sir in showdown process we have another option called SHUTDOWN TRANSATIONAL may be you missed it thanking u sir for stuff…………..

    • ram
      January 22, 2013 at 2:22 pm

      Good collection of ques,is there any jobs for 1year experienced,please send to my mail about dba jobs & dba interview questions ramsanikommu444@gmail.com thank u

  58. mohan
    January 17, 2013 at 7:36 am


  59. February 5, 2013 at 3:02 am

    hi if u have interview questions for freshers & exp can u forward me

  60. February 5, 2013 at 3:03 am

    any dba vacancies means pls inform me’

  61. Mohanraj
    February 22, 2013 at 11:31 am

    very nice tips , and one more thing is here , Am trying as a 3+ DBA , so plz if ur having the another articles plz forward to me, the real time scenarios also. And if there is any Oracle DBA openings plz let me know.

  62. Vinothkumar R
    March 14, 2013 at 1:07 pm

    I’m about to complete 3years as a Oracle DBA, your stuffs are really worth, thanks for sharing

  63. April 1, 2013 at 2:49 pm

    very very useful stuff…thanks for posting…

  64. ะŸะปัŽั
    April 19, 2013 at 6:08 am

    “5.3 Q- The result of the logical comparison (NULL = NULL) will beโ€ฆ And in the case of (NULL != NULL)
    A- False in both cases”
    The result will be NULL !!!!, not false.

  65. Neha Verma
    May 8, 2013 at 6:23 am

    please mention one more question
    “Tell me about yourself”
    “what is your daily task”
    I am 3+ yr of exp but I have always new task to do ๐Ÿ˜ฆ

  66. pankaj khatri
    May 15, 2013 at 3:34 am

    Very useful blog…. Enjoyed a lot and got many new things….
    Waiting for more new articles regarding DBA…..

  67. shivaji
    May 17, 2013 at 2:24 pm

    tanq very much……
    all v r expecting some more updates from u sir……

  68. sirisha
    June 19, 2013 at 12:23 pm

    it is good, and its very usefull for me…….

  69. Priyabrata
    August 1, 2013 at 4:52 am

    I just want one more answer for the below question
    what is the command for keeping the back-up?

  70. Pratibha
    September 19, 2013 at 1:27 pm

    good one

  71. Sarang
    October 5, 2013 at 6:13 pm

    Good job Mario. Would like to thank you from my heart.

  72. eswar
    November 21, 2013 at 4:27 pm

    Thanks Mario… so useful information for me… keep update with real-time Questions…

  73. December 5, 2013 at 10:10 am


    I really want tell you thanks. Very nice article you post. Also very useful to every one.
    Thanks for sharing.

  74. Nena
    January 6, 2014 at 2:25 pm

    Hi , Good Stuffs
    I also gather share some good website , where you find get articles related to oracle dba and apps dba , kinldy have a look.


  75. naresh reddy
    February 25, 2014 at 4:11 pm

    excellent article mario!! hope will u add more articles, please let me know more about scenario’s
    please can u forward me details of document for further steps
    Email : Naresh.rachupalli@gmail.com

    Thank you
    Naresh Reddy

  76. Sandeep
    June 8, 2014 at 7:37 am

    really its very useful…if any opportunity for DBA please let me know.

  77. July 1, 2014 at 5:44 pm

    Excellent article . Really it is useful to clear any type of DBA interviews.
    Oracle Training in Chennai

  78. sunil
    January 9, 2015 at 12:58 pm

    thank you very much….if u have some dataguard , upgradation and patching related interview questions then post it plz

  79. nagesh
    March 20, 2015 at 6:42 pm

    Excellent mr.mario…..,very nice interview questions please send more interview questions and real time senarios. nageshavasarala@gmail.com this is my mail-id please send me mr.mario….. . Excellent your article. I hope and i am waiting your response.

  80. Abhishek singha
    May 15, 2015 at 7:06 pm

    too good article

  81. Sachin Tyagi
    May 29, 2015 at 5:54 am

    It’s really helped in my interview ….Thanks for sharing it again here
    Sachin Tyagi
    Oracle DBA

  82. satya
    July 20, 2015 at 2:18 pm



  83. August 5, 2015 at 1:50 pm

    useful me thanks

  84. Natwar
    January 23, 2016 at 12:12 am

    Excellent article.. Simple and short.

  85. Venky
    March 11, 2016 at 6:58 pm

    thank you very much,
    its very usefull …..

  86. Mohsin Patel
    March 23, 2016 at 2:18 am

    Very useful


  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: