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?
A- MANAGER / CHANGE_ON_INSTALL
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?
A- DESCRIBE or DESC
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
A- DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DCL: GRANT, REVOKE
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?
A- DICT or DICTIONARY
2.9 Q- How can you view all the users account in the database?
A- SELECT USERNAME FROM DBA_USERS;
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?
A- PMON
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- SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;
3.4 Q- Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT
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?
A- 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.
5.8 Q- SQL> SELECT * FROM MY_SCHEMA.MY_TABLE;
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?
A- _ALLOW_RESETLOGS_CORRUPTION
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.
Mario,
nice article.
Adios
Dik Pater.
The Netherlands.
Thank you Dik, I’m glad you enjoyed it
Good Stuff- Really worthy
Thanks,
Ragav
thank you …..
donw good job
u have any new stuff forwarad to me
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
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.
Very useful information! Thanks a lot!!
thank you
very interesting and useful info
Thank you, very useful
thank u very much Mr.mario……,it is really of great help,can u post some more questions and realtime tools plz
Sure Md.Asif, I will update the blog to add some new posts about realtime tools
i need some more question abut performance tuning …
I am also want more questions about perfomance tuning
very very nice questions
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..
DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DCL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DDL: GRANT, REVOKE
Please Let me know i am right or wrong !
I think you got this wrong:
2.4 Q- Say two examples of DML, two of DCL and two of DDL
A- DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DCL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DDL: GRANT, REVOKE
It should be:
…
DDL: CREATE, ALTER, DROP,…
DCL: GRANT, REVOKE
Thanks for the list.
Vu and sowteer: You are right, I made a mistake and misplaced DCL and DDL. It’s already fixed, thank you for your contribution!
I think you have forgotten in the DCL statement list ROLLBACK, COMMIT and SAVEPOINT.
Dear ,
I think SELECT is not DML command ,It’s belonging to DQL
Regards,
Ratnesh
select is a DRL command..
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..
Shruti…
Contratulations for your successfully interview! I’m glad to have helped you, more tips will come soon
Very good questions and answers…Recenly I attended DBA interview all the HR -Techincal questions are covered here… Thanks
good in knowledge improvise it a simple overview on database nice
Thank you, very useful
MURALI K FROM INDIA, HYDERABAD.
hii murali nice to meet you for dba do we need any work experience???
its very really works please mario if you have an opening for dba as fresher so can you mail me once
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 11.2.0.1.0 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:
Connected.
SQL>
SQL>
SQL>
SQL> select tablespace_name,USER_BYTES/1024/1024/1024 size_gb from dba_temp_file
s;
TABLESPACE_NAME SIZE_GB
—————————— ———-
TEMP 12.9990234
SQL>
SQL>
SQL>
SQL> alter database tempfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\STLBAS\TEMP01.DBF’
resize 10g;
Database altered.
SQL>
SQL>
SQL> select tablespace_name,USER_BYTES/1024/1024/1024 size_gb from dba_temp_file
s;
TABLESPACE_NAME SIZE_GB
—————————— ———-
TEMP 9.99902344
SQL>
SQL>
regards
Halim
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 🙂
i want to know that your database release?you can tell me?
Thanks
very good help i got for my interviews
Thanks……….helped me alot for my interview
I’m glad I could help you, and I hope you get the job 🙂
nice questions pls send me more questions.thank u.
very use ful questions for dba candidates
such a nice collection. keep going on.
Please stste more question regarding RMAN
thnx……..its really very useful
Nice QA,,,,please send more QA. Veera from Bangalore.
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
Very nice article. Highly appreciated
Thanks, A lot it really helpful for the interview…
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.
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.
Manya many thanx, and please send me many more Q&A.. if u know openings in Oracle DBA, plz intimate dba.psrinivas@gmail.com
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..
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.
Thanku….
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 ,
Regards,
Debojyoti
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
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 ,
Debojyoti
good work
Very Excellent article
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 ?
excellent article
this article gives basics of oracle dba. i want more.
Thanks very excellent
very usefull for dba cader
good questions. thank u so much for helping me out.
Excellent !!
very nice material………..
Thank you very much.
really worty
very nice dba interview questions but these are very easy one plstry to post some difficult questions
Hi,
This is Bhaskar, i want answer for one question…that is
How do know that database has crashed?
please help…me…any one…
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!
Thanks for such a valuable share 🙂
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
Thanks for your info…its really useful to me…………
Thanks very good oracle questions .
good questions
nice article…
Hi sir in showdown process we have another option called SHUTDOWN TRANSATIONAL may be you missed it thanking u sir for stuff…………..
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
thanq..
hi if u have interview questions for freshers & exp can u forward me
any dba vacancies means pls inform me’
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.
I’m about to complete 3years as a Oracle DBA, your stuffs are really worth, thanks for sharing
very very useful stuff…thanks for posting…
“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.
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 😦
Very useful blog…. Enjoyed a lot and got many new things….
Waiting for more new articles regarding DBA…..
tanq very much……
all v r expecting some more updates from u sir……
it is good, and its very usefull for me…….
I just want one more answer for the below question
what is the command for keeping the back-up?
good one
Good job Mario. Would like to thank you from my heart.
Thanks Mario… so useful information for me… keep update with real-time Questions…
Hi,
I really want tell you thanks. Very nice article you post. Also very useful to every one.
Thanks for sharing.
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.
http://goo.gl/LvOvhf
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
really its very useful…if any opportunity for DBA please let me know.
Excellent article . Really it is useful to clear any type of DBA interviews.
Oracle Training in Chennai
thank you very much….if u have some dataguard , upgradation and patching related interview questions then post it plz
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.
too good article
It’s really helped in my interview ….Thanks for sharing it again here
Sachin Tyagi
Oracle DBA
THANKX……
exCELLENT ARTICLE….
useful me thanks
Excellent article.. Simple and short.
thank you very much,
its very usefull …..
Very useful
Thanks
Please help me to get more interview questions for 1 year experience person