7 Interview Questions

becvx · 发表于 2008-9-2 19:32

转贴：

很好的一篇文章，和大家分享。

Hiring a DBA?
7 Interview Questions
by Sean Hull

Migrating to Oracle Database 11g, An Internet.com IT Management eBook.
© 2007, Jupitermedia Corp.

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 conceptual, 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 tens of thousands
of attempts quickly, this can be a very effective
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 10 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. 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 five
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. Any
user in the database can access a global one.
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.