Oracle Interview Questions
Version 12.1.0.1

General Information
I am quite frankly amazed at the low quality of questions asked during interviews.

This page is a compilation of questions you can expect from me during an interview for an Oracle-related position. They are not intended to be complete but rather to highlight things that are important and rarely asked.

Will there be any answers posted here? Only if you pay in Credit Suisse bars, fine single-malt scotch, and/or via a consulting contract.
 
DBA Questions
The following questions have been developed to afford a reasonable view into the skills, experience, and qualifications of someone applying for the position of Oracle DBA.

Relevant follow-up questions should be assumed.
  1. If you are assigned to managing 10-20 production databases. What tools will you want and why?
  2. Assuming the above ... design your backup strategy on the white board. Ask any questions required to gather the information necessary to do so.
  3. Assuming the above ... what are you going to do to make sure these databases are properly configured for security, stability, scalability, and high availability?
  4. Assuming the above ... describe your normal activities from the beginning to the end of your work day.
  5. What is Jonathan Lewis' most recent book about and what was the most important thing you learned from reading it?
  6. What is Cary Millsap's most recent book about and what is the most important thing you learned from reading it?
  7. What was the last class you took to improve your skills and what is the most important thing you learned from it?
  8. Describe the best and worst days of your career as a DBA.
  9. Someone just called you on the phone and told you that no one can connect to the Accounts Payable application. Describe, in detail,  what you will do to resolve the issue?
  10. You just got an email from the help desk saying the Distribution system is running unacceptably slowly. Describe, in detail, what you will do to resolve the issue?
  11. A table in your DSS system has a bitmap index on one column. You update one byte in one row. How much redo will be generated and why?
  12. What are the issues related to running nologging transactions on a production server?
  13. What are the implications of a database with multiple data block sizes? When might you choose non-standard block sizes?
  14. Describe, in detail, what happens when the following commands are issued in sequence: STARTUP NOMOUNT, ALTER DATABASE MOUNT, ALTER DATABASE OPEN, SHUTDOWN NORMAL. In what version of Oracle would your answer not be correct?
  15. What is the worst system failure you have experienced in your career as a DBA and what was your part in resolving it?
  16. What is your favorite new feature in 11gR2 ... and why?
  17. What will you do if after I hire you I ask you to grant the DBA privilege to the VP of Marketing?
  18. An employee just emailed you that they just, minutes before, deleted all of the rows in a configuration table. What will you do?
  19. If the position involves RAC: In what way is Oracle's claim that any application can run on RAC not true?
  20. If the position involves DataGuard: Why might you want to have more than one listener on each server?
  21. What are the best and worst aspects of working with MyOracleSupport?
  22. Describe the steps you would use to apply a PSU or CPU to a database.
  23. What is an RDA? Review this alert log <alert log provided by interviewer> and tell me what you see of interest.
  24. Review this AWR report <AWR report provided by interviewer> and tell me what you see of interst.
  25. What is Edition Based Redefinition and what does it do?
  26. If you created a database user named C##TOOLS why did you name it this way?
  27. Please go up to the white board and write a functional package header and body containing one stored procedure and one function including exception handling.
 
SQL & PL/SQL Developer Questions
The following questions have been developed to afford a reasonable view into the skills, experience, and qualifications of someone applying for the position of SQL and/or PL/SQL Developer.

Relevant follow-up questions should be assumed.
  1. When is it appropriate to use PL/SQL rather than SQL?
  2. When is it appropriate to use dynamic rather than static SQL?
  3. What is the difference between an implicit cursor and an explicit cursor? Write an example of each on the whiteboard.
  4. Explain the Oracle transaction model including MVCC and SCNs.
  5. Explain the Oracle locking model and the proper use and variants of the FOR UPDATE statement.
  6. Your code is generating ORA-01555s. What are the possible causes?
  7. How do the SAVE EXCEPTIONS and LIMITS clauses work?
  8. What is the last book you read to improve your development skills and what is the most important new thing you learned from it?
  9. What as the last class you took to improve your development skills and what is the most important new thing you learned from it?
  10. Why does Tom Kyte say that "triggers are evil?"
  11. Explain how SQL Injection works and how you would prevent it.
  12. What are your four favorite Oracle supplied built-in packages and how have you used them?
  13. What is partition pruning and how does it work? Provide an example of where you would expect it to not work.
  14. What is DBMS_XPLAN and how have you used it?
  15. Under what situations would you consider the use of a function based index?
  16. What is the biggest technical mistake you have made during your career as an Oracle developer?
  17. What is your favorite new feature in 11gR2 and why?
  18. What is your favorite new feature in 12cR1 and why?
  19. Which PL/SQL warning has been most valuable to you?
  20. What would you do if an explain plan indicated that your code was very fast but when run it took hours to complete?
  21. Which hints do you regularly use and why?
  22. What is a pragma and how have you used them?
  23. Please take a look at this code sample <code_provided by interviewer>. How would you improve it?
  24. Please take a look at this explain plan <plan_provided by interviewer> and describe what you see in the order of execution.
 
DBA Manager Questions
The following questions have been developed to afford a reasonable view into the skills, experience, and qualifications of someone applying for the position of DBA Manager: Not from the standpoint of managing people but from the standpoint of specifically hiring and managing DBAs.

Relevant follow-up questions should be assumed.
  1. Due to an accident a table has been lost from a production database. Your two most senior DBAs come to you with different solutions. One wants to recover the table from last night's backup. The other wants to recover it from the recyclebin. What do you do?
  2. The organization runs an ecommerce business that takes orders for contact lenses on its website. Describe the landscape from a compliance, governance, and regulatory point of view and the specific issues you, and your team, will need to address.
  3. The company has decided to move from generic 1U servers to ODAs and Exadata and to implement Edition Based Redefinition in 12cR1. No one on the current team has the required skill sets. Please write a job description on the white board and describe how you would interview candidates?
  4. What is your responsibility with respect to servers, storage, and networking?
  5. Who is responsible for keeping the team's skill set current?
  6. What have you done during your career to keep your skill set current with respect to the highly technical teams you've managed?
  7. What operating systems do you think are appropriate for an Oracle database and why?
  8. What are the plusses and minuses with respect to full backups versus incremental backups?
  9. Four nines of means a system can be unavailable for how many minutes per year?
  10. It is 2:30am and a production server is down. What instructions does your team have? Where are you? What are you doing? How will your team reach you?
  11. You have a team of DBAs in the local office and a team at a remote location. A local DBA has changed the password of a critical account. How do you communicate the new password to the remote location?

Related Topics
Morgan's Library

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved