练练手:Oracle面试题

recently went on an interview for Senior Oracle DBA position. Prior the interview a quiz was required, which I passed without any problems.

It might be interesting for you to see the nature of the questions and the topics covered. If you feel you know the answer, post a comment with the question number. I will eventually post my answers as well. Enjoy this brain storm..

Courtesy of our member: tyager

DBA routine
-----------

[1] Here are some categories of parameters for routine monitoring. Can you suggest any more? How would you monitor each?

Memory
Storage
Disk I/O

Networking
----------
[1] Say an application server uses a pool of 20 database connections. They are shared among some hundreds of concurrent users, so each connection is active most of the time. The database is on another machine. How would you use Multi Threaded Server in these circumstances?

[2] What's going on here?

36 $ tnsping TEST01

TNS Ping Utility for Solaris: Version 8.1.7.0.0 - Production on
24-APR-2001 12:24:21

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.3)(PORT=1521))
OK (1 msec)

testuser@test-01 /data/testuser
337 $ sqlplus devuser_owner/dev@TEST01

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Apr 24 12:24:38 2001

(c) Copyright 2000 Oracle Corporation. All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not
exist

Tuning
------

[1] What's wrong here and how would you fix it?

SQL> select tablespace_name, segment_type, sum(bytes) / 1048576 Mb
2 from dba_segments where wner = 'DEVUSER_OWNER'
3 group by tablespace_name, segment_type;

TABLESPACE_NAME SEGMENT_TYPE MB
------------------------------ ------------------ ----------
INDX INDEX .75
USERS INDEX 30.25
USERS TABLE 16.375

3 rows selected.

[2] How can you monitor for performance problems in a specific Oracle session? Say an application is slow at displaying a particular page, but the developers aren't able to identify which SQL statments are the culprits. How could you help?

Shell
-----
[1] How do I find out the names of files in a tar file called
"arch1.tar"?

[2] Say I have a directory tree under '.' and I want to look for the names of text files called '*log' containing 'ORA-00054'. How could I do that?

[3] What's going on here?

$ ssh -n cyclops 'more /var/local/mail-list/*'

[4] What command would you use to display which user owns the current directory?

Solaris
-------

[1] What is the meaning of the "smtx" column in the following output of the Solaris command "mpstat" and what impact might it have on Oracle?

CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt
idl
0 1 0 0 305 205 36 0 1 0 0 58 0 0 0
100

[2] What command(s) will show you the amount of available swap space available on a system?

[3] The Solaris kernel initially requires special configuration to accomodate Oracle. What needs to be done to it?

[4] Define ISM, explain how Oracle uses it, and tell why it's more efficient.

[5] Define KAIO, and explain how Oracle uses it.

[6] If you have the command "qiomkfile" available on your system, how did it get there, and what is its purpose?

[7] Explain what a filesystem snapshot is.

[8] What does the "coraenv" script. do?

[9] What does the "bshut" script. do?

[10] What is the purpose of the file "/var/opt/oracle/oratab" ?

Oracle Internals
----------------

[1] Explain the definition of a "star schema."

[2] Explain what a bitmapped index is, what it's applications would be, and why it's useful.

[3] Describe the procedure for moving a datafile from one disk to another. Show actual SQL used for this procedure.

[4] Explain the difference between a "hot" and a "cold" backup. What is involved with executing a "hot" backup?

[5] Explain the differences between logical and physical database backups. What are the benefits and penalties of each?

[6] If an underlying filesystem uses a blocksize of 2K, what would the optimal blocksize be for the STORAGE clause in a CREATE TABLESPACE
statement?

[7] What variables in the "init.ora" file affect the amount of RAM needed for the SGA?

[8] What is the SQL statement necessary to delete a user named "joe" and everything that he owns in a database?

[9] If you were connected to a database, TEST01, as the SYS user, what would be a SQL statement that you could use to show the members of all of
TEST01's redo log groups?

[10] If you had a tablespace, TEST_TABLESPACE, which consists of three files: TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to delete the file TEST02.dbf, what else would you need
in order to recover all the data that was present in TEST_TABLESPACE at the time that TEST02.dbf was deleted?

[11] You notice the machine that is running a database called TEST01 is running about 95-100% idle. You have about 30-45 users trying to connect
to TEST01, and they are complaining that the database is slow to respond, or doesn't respond at all. Where do you being your diagnosis, and what
steps do you take to resolve the problem?

[12] What's the default password for the Oracle user "scott" ?

[13] How would you get the Oracle server to show you its plan for a given SQL statement?

[14] Explain what an Oracle snapshot is.

[15] What is a partitioned table?