There are nearly an infinite number and combination of
questions one can pose to a DBA candidate in an interview. I prefer to lean
towards the conceptional, rather than the rote, as questions of this kind
emphasize your foundation, and thorough understanding. Besides, I’ve never
been one to remember facts and details I can lookup in a reference. Therefore,
with that in mind, here are some brainteasers for you to ponder over.
1. Why is a UNION ALL faster than a UNION?
The union operation, you will
recall, brings two sets of data together. It will *NOT* however produce
duplicate or redundant rows. To perform this feat of magic, a SORT operation
is done on both tables. This is obviously computationally intensive, and uses
significant memory as well. A UNION ALL conversely just dumps collection of
both sets together in random order, not worrying about duplicates.
2. What are some advantages to using Oracle’s CREATE
DATABASE statement to create a new database manually?
-
You can script the process to
include it in a set of install scripts you deliver with a product. -
You can put your create database
script in CVS for version control, so as you make changes or adjustments to it,
you can track them like you do changes to software code. -
You can log the output and review
it for errors. -
You learn more about the process
of database creation, such as what options are available and why.
3. What are three rules of thumb to create good
passwords? How would a DBA enforce those rules in Oracle? What business
challenges might you encounter?
Typical password cracking software
uses a dictionary in the local language, as well as a list of proper names, and
combinations thereof to attempt to guess unknown passwords. Since computers
can churn through 10’s of thousands of attempts quickly, this can be a very
affective way to break into a database. A good password therefore should not
be a dictionary word, it should not be a proper name, birthday, or other
obvious guessable information. It should also be of sufficient length, such as
eight to ten characters, including upper and lowercase, special characters, and
even alternate characters if possible.
Oracle has a facility called
password security profiles. When installed they can enforce complexity, and
length rules as well as other password related security measures.
In the security arena, passwords
can be made better, and it is a fairly solvable problem. However, what about
in the real-world? Often the biggest challenge is in implementing a set of
rules like this in the enterprise. There will likely be a lot of resistance to
this, as it creates additional hassles for users of the system who may not be
used to thinking about security seriously. Educating business folks about the
real risks, by coming up with real stories of vulnerabilities and break-ins you’ve
encountered on the job, or those discussed on the internet goes a long way
towards emphasizing what is at stake.
4. Describe the Oracle Wait Interface, how it works, and
what it provides. What are some limitations? What do the db_file_sequential_read
and db_file_scattered_read events indicate?
The Oracle Wait Interface refers to
Oracle’s data dictionary for managing wait events. Selecting from tables such
as v$system_event and v$session_event give you event totals through the life of
the database (or session). The former are totals for the whole system, and
latter on a per session basis. The event db_file_sequential_read refers to
single block reads, and table accesses by rowid. db_file_scattered_read
conversely refers to full table scans. It is so named because the blocks are
read, and scattered into the buffer cache.
5. How do you return the top-N results of a query in
Oracle? Why doesn’t the obvious method work?
Most people think of using the
ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined
*before* the ORDER BY so you don’t get the results you want. The answer is to
use a subquery to do the ORDER BY first. For example to return the top-5
employees by salary:
SELECT * FROM
(SELECT * FROM employees ORDER BY salary)
WHERE ROWNUM < 5;
6. Can Oracle’s Data Guard be used on Standard Edition,
and if so how? How can you test that the standby database is in sync?
Oracle’s Data Guard technology
is a layer of software and automation built on top of the standby database
facility. In Oracle Standard Edition it is possible to be a standby database,
and update it *manually*. Roughly, put your production database in archivelog
mode. Create a hotbackup of the database and move it to the standby machine.
Then create a standby controlfile on the production machine, and ship that
file, along with all the archived redolog files to the standby server. Once
you have all these files assembled, place them in their proper locations,
recover the standby database, and you’re ready to roll. From this point on,
you must manually ship, and manually apply those archived redologs to stay in
sync with production.
To test your standby database, make
a change to a table on the production server, and commit the change. Then
manually switch a logfile so those changes are archived. Manually ship the
newest archived redolog file, and manually apply it on the standby database.
Then open your standby database in read-only mode, and select from your changed
table to verify those changes are available. Once you’re done, shutdown your
standby and startup again in standby mode.
7. What is a database link? What is the difference
between a public and a private database link? What is a fixed user database
link?
A database link allows you to make
a connection with a remote database, Oracle or not, and query tables from it,
even incorporating those accesses with joins to local tables.
A private database link only works
for, and is accessible to the user/schema that owns it. A global one can be
accessed by any user in the database.
A fixed user link specifies that
you will connect to the remote db as one and only one user that is defined in
the link. Alternatively, a current user database link will connect as the
current user you are logged in as.
As you prepare for your DBA Interview, or prepare to give
one, we hope these questions provide some new ideas and directions for your
study. Keep in mind that there are a lot of directions an interview can
go. As a DBA emphasize what you know, even if it is not the direct answer
to the question, and as an interviewee, allow the interview to go in creative
directions. In the end, what is important is potential or aptitude, not
specific memorized answers. So listen for problem solving ability, and
thinking outside the box, and you will surely find or be the candidate for the
job.
