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
- What
DBA activities did you to do today? - What
is your typical day like? - What
other parts of your organization do you interact with and how? - Do
you consider yourself a development DBA or a production DBA and why? - Are
you a nuts-n-bolts DBA or a tools-n-props DBA
- Explain
the difference between a hot backup and a cold backup and the benefits
associated with each - You
have just had to restore from backup and do not have any control files. How
would you go about bringing up this database? - How
do you switch from an init.ora file to a spfile? - Explain
the difference between a data block, an extent and a segment. - Give
two examples of how you might determine the structure of the table DEPT. - Where
would you look for errors from the database engine? - Compare
and contrast TRUNCATE and DELETE for a table. - Give
the reasoning behind using an index. - Give
the two types of tables involved in producing a star schema and the type of
data they hold. - . What type of index should
you use on a fact table? - . Give two examples of
referential integrity constraints. - 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? - Explain the difference between
ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to
each. - What command would you use
to create a backup control file? - Give the stages of instance
startup to a usable state where normal users may access it. - What column differentiates
the V$ views to the GV$ views and how? - How would you go about
generating an EXPLAIN plan? - How would you go about
increasing the buffer cache hit ratio? - Explain an ORA-01555
- Explain the difference
between $ORACLE_HOME and $ORACLE_BASE. - How would you determine the
time zone under which a database was operating? - Explain the use of setting
GLOBAL_NAMES equal to TRUE. - What command would you use
to encrypt a PL/SQL application? - Explain the difference
between a FUNCTION, PROCEDURE and PACKAGE. - Explain the use of table
functions. - Name three advisory
statistics you can collect. - Where in the Oracle
directory tree structure are audit traces placed? - Explain materialized views
and how they are used. - When a user process fails,
what background process cleans up after it? - What background process
refreshes materialized views? - How would you determine what
sessions are connected and what resources they are waiting for? - Describe what redo logs are.
- How would you force a log
switch? - Give two methods you could
use to determine what DDL changes have been made. - What does coalescing a
tablespace do? - What is the difference
between a TEMPORARY tablespace and a PERMANENT tablespace? - Name a tablespace
automatically created when you create a database. - When creating a user, what
permissions must you grant to allow them to connect to the database? - How do you add a data file
to a tablespace? - How do you resize a data
file? - What view would you use to
look at the size of a data file? - What view would you use to
determine free space in a tablespace? - How would you determine who
has added a row to a table? - How can you rebuild an
index? - Explain what partitioning is
and what its benefit is. - You have just compiled a
PL/SQL package but got errors, how would you view the errors? - How can you gather
statistics on a table? - How can you enable a trace
for a session? - What is the difference
between the SQL*Loader and IMPORT utilities? - Name two files used for
network connection to a database.
Technical – UNIX
- How
do you list the files in an UNIX directory while also showing hidden files? - How
do you execute a UNIX command in the background? - What
UNIX command will control the default file permissions when files are created? - Explain
the read, write, and execute permissions on a UNIX directory. - What
is the difference between a soft link and a hard link? - Give
the command to display space usage on the UNIX file system. - Explain
iostat, vmstat and netstat. - How
would you change all occurrences of a value using VI? - Give
two UNIX kernel parameters that effect an Oracle install - 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!
