Oracle Technical Interview

Have you been thinking about switching jobs and are uncertain
about that technical interview process? Try taking my technical interview and
see if you are up it.

It seems the job market is starting to move a bit. If you
are uncomfortable in your current position or if you just want to test the
waters, it might be time to brush off your resume and hit the road. Before you
do, you should also brush up on some of your DBA skills so that you are ready
for the dreaded technical interview. Here is a list of questions I think any
true DBA should be able to answer. If you do not know the answers to these
questions, not to fear, take a bit of time, research the answers, and then
go and hit the road. Some of the questions are about you, your personality and
how you conduct yourself. Other questions are technical in nature concerning
Oracle and operating systems. When answering the questions, both when you go
through this interview and when you are in a real interview, try to answer
completely and include any real-life experiences you have. Just answering
questions with a yes/no or minimum answer does not show a true interest in the
field of database administration and how it can impact the company. Most people
want to know how you apply the knowledge and what you have learned from it.

Personal

  1. What
    DBA activities did you to do today?

  2. What
    is your typical day like?

  3. What
    other parts of your organization do you interact with and how?

  4. Do
    you consider yourself a development DBA or a production DBA and why?

  5. Are
    you a nuts-n-bolts DBA or a tools-n-props DBA

Technical – Oracle

  1. Explain
    the difference between a hot backup and a cold backup and the benefits
    associated with each

  2. You
    have just had to restore from backup and do not have any control files. How
    would you go about bringing up this database?

  3. How
    do you switch from an init.ora file to a spfile?

  4. Explain
    the difference between a data block, an extent and a segment.

  5. Give
    two examples of how you might determine the structure of the table DEPT.

  6. Where
    would you look for errors from the database engine?

  7. Compare
    and contrast TRUNCATE and DELETE for a table.

  8. Give
    the reasoning behind using an index.

  9. Give
    the two types of tables involved in producing a star schema and the type of
    data they hold.

  10. . What type of index should
    you use on a fact table?

  11. . Give two examples of
    referential integrity constraints.

  12. A table is classified as a
    parent table and you want to drop and re-create it. How would you do this
    without affecting the children tables?

  13. Explain the difference between
    ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to
    each.

  14. What command would you use
    to create a backup control file?

  15. Give the stages of instance
    startup to a usable state where normal users may access it.

  16. What column differentiates
    the V$ views to the GV$ views and how?

  17. How would you go about
    generating an EXPLAIN plan?

  18. How would you go about
    increasing the buffer cache hit ratio?

  19. Explain an ORA-01555
  20. Explain the difference
    between $ORACLE_HOME and $ORACLE_BASE.

  21. How would you determine the
    time zone under which a database was operating?

  22. Explain the use of setting
    GLOBAL_NAMES equal to TRUE.

  23. What command would you use
    to encrypt a PL/SQL application?

  24. Explain the difference
    between a FUNCTION, PROCEDURE and PACKAGE.

  25. Explain the use of table
    functions.

  26. Name three advisory
    statistics you can collect.

  27. Where in the Oracle
    directory tree structure are audit traces placed?

  28. Explain materialized views
    and how they are used.

  29. When a user process fails,
    what background process cleans up after it?

  30. What background process
    refreshes materialized views?

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

  32. Describe what redo logs are.
  33. How would you force a log
    switch?

  34. Give two methods you could
    use to determine what DDL changes have been made.

  35. What does coalescing a
    tablespace do?

  36. What is the difference
    between a TEMPORARY tablespace and a PERMANENT tablespace?

  37. Name a tablespace
    automatically created when you create a database.

  38. When creating a user, what
    permissions must you grant to allow them to connect to the database?

  39. How do you add a data file
    to a tablespace?

  40. How do you resize a data
    file?

  41. What view would you use to
    look at the size of a data file?

  42. What view would you use to
    determine free space in a tablespace?

  43. How would you determine who
    has added a row to a table?

  44. How can you rebuild an
    index?

  45. Explain what partitioning is
    and what its benefit is.

  46. You have just compiled a
    PL/SQL package but got errors, how would you view the errors?

  47. How can you gather
    statistics on a table?

  48. How can you enable a trace
    for a session?

  49. What is the difference
    between the SQL*Loader and IMPORT utilities?

  50. Name two files used for
    network connection to a database.

Technical – UNIX

  1. How
    do you list the files in an UNIX directory while also showing hidden files?

  2. How
    do you execute a UNIX command in the background?

  3. What
    UNIX command will control the default file permissions when files are created?

  4. Explain
    the read, write, and execute permissions on a UNIX directory.

  5. What
    is the difference between a soft link and a hard link?

  6. Give
    the command to display space usage on the UNIX file system.

  7. Explain
    iostat, vmstat and netstat.

  8. How
    would you change all occurrences of a value using VI?

  9. Give
    two UNIX kernel parameters that effect an Oracle install

  10. Briefly, how do you install
    Oracle software on UNIX.

I hope that these interview questions were not too hard.
Remember these are “core” DBA questions and not necessarily related to the
Oracle options that you may encounter in some interviews. Take a close look at
the requirements for any job and try to extract questions that they may ask
from manuals and real life experiences. For instance, if they are looking for a
DBA to run their databases in RAC environments, you should try to determine
what hardware and software they are using BEFORE you get to the interview. This
would allow you to brush up on particular environments and not be caught
off-guard. Good luck!

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles