Oracle DBA Interview Questions By B G
1. How many memory layers are in the shared pool?
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
3. How can you tell how much space is left on a given file system and how much space each of the file system's subdirectories take-up?
4. Define the SGA and：
? How you would configure SGA for a mid-sized OLTP environment?
? What is involved in tuning the SGA?
5. What is the cache hit ratio， what impact does it have on performance of an Oracle database and what is involved in tuning it?
6. Other than making use of the statspack utility， what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
7. How do you tell what your machine name is and what is its IP address?
8. How would you go about verifying the network name that the local_listener is currently using?
9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?
11. What is the recommended interval at which to run statspack snapshots， and why?
12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index， even if the index scan may appear to be calculated as more costly?
13. Assuming today is Monday， how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
17. How would you best determine why your MVIEW couldn't FAST REFRESH?
18. How does propagation differ between Advanced Replication and Snapshot Replication (readonly)?
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
20. How would you begin to troubleshoot an ORA-3113 error?
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed"， how do you approach troubleshooting this issue?
23. How would you extract DDL of a table without using a GUI tool?
24. You're getting high "busy buffer waits" - how can you find what's causing it?
25. What query tells you how much space a tablespace named "test" is taking up， and how much space is remaining?
26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle's. What database recovery options are available? Database is in archive log mode.
28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris)。
29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?
30. Provide an example of a shell script which logs into SQLPLUS as SYS， determines the current date， changes the date format to include minutes & seconds， issues a drop table command， displays the date again， and finally exits.
31. Explain how you would restore a database using RMAN to Point in Time?
32. How does Oracle guarantee data integrity of data changes?
33. Which environment variables are absolutely critical in order to run the OUI?
34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
35. Why does Oracle not permit the use of PCTUSED with indexes?
36. What would you use to improve performance on an insert statement that places millions of rows into that table?
37. What would you do with an "in-doubt" distributed transaction?
38. What are the commands you'd issue to show the explain plan for "select * from dual"?
39. In what script is "snap$" created? In what script is the "scott/tiger" schema created?
40. If you're unsure in which script a sys or system-owned object is created， but you know it's in a script from a specific directory， what UNIX command from that directory structure can you run to find your answer?
41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
42. You create a private database link and upon connection，
fails with： ORA-2085： connects to .
What is the problem? How would you go about resolving this error?
43. I have my backup RMAN script called "backup_rman.sh". I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
44. Explain the concept of the DUAL table.
45. What are the ways tablespaces can be managed and how do they differ?
46. From the database level， how can you tell under which time zone a database is operating?
47. What's the benefit of "dbms_stats" over "analyze"?
48. Typically， where is the conventional directory structure chosen for Oracle binaries to reside?
49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.
答案：
1. How many memory layers are in the shared pool?
主要有以下几块组成, fixed tunnable variable (processes,sessions,transactions,dml_locks,buffer headers).
library cache (parsed sql /plsql representation, the main purpose of shared pool).
row cache /dictionary cache (cache of data dictionary rows).
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
I don't know , just backup archivelog all not backup 1 time.
3. How can you tell how much space is left on a given file system and how much space each of the file system's subdirectories take-up?
df -k
du -k
4. Define the SGA and：
? How you would configure SGA for a mid-sized OLTP environment?
use proper log buffer .normally 512k/1m
use proper shared pool size normally 150m-200m.
if not use jave , set java_pool_size to 0
if not use rman/mts/parallel set large_pool_size to minimize , normally 8m
the other memory set to buffer cache, the method is below.
? What is involved in tuning the SGA?
put different data into different buffer pools,small frequently used tables put into keep pool.
large seldom used tables put into recycle pool. others mainly put into default pool. if some table physical reads is enomous, then consider to put some segments(index/data) into keep pool.
if possible use 40%-60% of memory for buffer cache.
5. What is the cache hit ratio， what impact does it have on performance of an Oracle database and what is involved in tuning it?
cache hit ration = 1 - physical reads/(consistents gets + db block gets)
consistents gets refers to select query or none dml part of a dml statement.
db blocks gets refers to blocks that have be modified/created .
phisical reads refers to blocks that reads directly from disks.
logical reads are blocks that directly read from memory.
if this ratio is too small , may the system have too much disk reads, we should consider to tune the memory usage, and try to tune the sql statements with so much disk reads.
but now we prefer to use the wait events to tune the system. I prefer to use top 5/10 events togather with top 5/10 logical/disk reads to tune the system.
bad sql is the cause 90% system problems .
6. Other than making use of the statspack utility， what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
1). tablespace usage. dba_free_space
2). invalidate objects/indexes. dba_objects/dba_indexes
3). top large data segments / top large index segment dba_segments
4). active transaction list. v$transaction
5) active session list v$session.active
6) active lock list v$lock
7) session distribution between machines/applications. v$session, group by machine,owner,idle_time
8). system resource usage v$resource_usage
7. How do you tell what your machine name is and what is its IP address?
hostname
ifconfig
nerstat -nr
uname -r
8. How would you go about verifying the network name that the local_listener is currently using?
tnsping network_name;
ping ip_address.
sqlplus
9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
ipcs
ps -ef
10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?
v$process.addr = v$session.paddr
11. What is the recommended interval at which to run statspack snapshots， and why?
15-30 minutes at busy time. the statistics can show the spot of the system. and also will not be wipe out by useless information of the system.
12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index， even if the index scan may appear to be calculated as more costly?
not clearly . must use show parameter optimize.
show parameter index
13. Assuming today is Monday， how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
conn scott
var job_no number
dbms_job.submit(:job_no,trunc(sysdate+3+9/24),'trunc(sysdate+1) + 2/24');
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
crontab -e
#add new job
0 14 * * * /test/test.sh >/tmp/test.log 2>&1
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
dont know.
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
dba/user_mviews
dba_jobs/user_jobs.(refer to the specified mview).
17. How would you best determine why your MVIEW couldn't FAST REFRESH?
1) is there any mview log.
2) is therer any primary key exists.
18. How does propagation differ between Advanced Replication and Snapshot Replication (readonly)?
dont know.
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
dont know
20. How would you begin to troubleshoot an ORA-3113 error?
1) is the network has some problem.
2) is the db has some problem.
3) has somebody killed the session.
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
v$lock/v$locked_objects/v$enqueue_lock/dba_locks/dba_ddl_locks/dba_lock_internals.
x$kgllk,x$kglpn(library cache lock/pins).
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed"， how do you approach troubleshooting this issue?
1) check the error no with alert_log. or jobq trace file.
2) check job parameters.
23. How would you extract DDL of a table without using a GUI tool?
exp rows=n
imp indexfile=aaa.sql
select dbms_metadata.get_ddl('table_name','owner','TABLE') from dual;
just generate it from dba_tables/dba_tab_columns/dba_tab_partitions/dba_part_tables/...
24. You're getting high "busy buffer waits" - how can you find what's causing it?
1) find the hot block. with the v$session_wait/trace file/v$bh
2) find the main sql statement .
3) tune it.
25. What query tells you how much space a tablespace named "test" is taking up， and how much space is remaining?
dba_free_spaces/dba_extents
26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
1) . first view active sessions. and v$session_wait .
2). see the uptime and top processes of the system.
3) analyze the main reason. and tune it.
27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle's. What database recovery options are available? Database is in archive log mode.
instance recovery
28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris)。
cat /proc/cpuinfo
top
29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?
umilit
30. Provide an example of a shell script which logs into SQLPLUS as SYS， determines the current date， changes the date format to include minutes & seconds， issues a drop table command， displays the date again， and finally exits.
sqlplus /nolog <<_END_
connect / as sysdba
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;
set time on timing on
drop table table_name;
select sysdate from dual;
exit;
_END_
31. Explain how you would restore a database using RMAN to Point in Time?
restore database until time '2006-08-18 00:00:00';
32. How does Oracle guarantee data integrity of data changes?
use constraints / isolation level
33. Which environment variables are absolutely critical in order to run the OUI?
DISPLAY
34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
select count(*) from v$session where username = '';
35. Why does Oracle not permit the use of PCTUSED with indexes?
dont know.
36. What would you use to improve performance on an insert statement that places millions of rows into that table?
1) at idle time.
2) direct insert /*+append*/
3) if possible nologging
4) if possible parallel
5) if possible disable constraints(foreign key/primary key)
6) if possible disable index.
37. What would you do with an "in-doubt" distributed transaction?
not clearly.
38. What are the commands you'd issue to show the explain plan for "select * from dual"?
explain plan for
select * from dual;
@?/rdbms/admin/utlxplp.sql
select * from (dbms_xplan.display)
39. In what script is "snap$" created? In what script is the "scott/tiger" schema created?
sql.bsq.
demo
40. If you're unsure in which script a sys or system-owned object is created， but you know it's in a script from a specific directory， what UNIX command from that directory structure can you run to find your answer?
$ORACLE_HOME/rdbms/admin/
$ORACLE_HOME/sqlplus/admin
41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
dss.ica
42. You create a private database link and upon connection，
fails with： ORA-2085： connects to .
oerr ora 2085 to know details of this errors.
use google to search some answers.
What is the problem? How would you go about resolving this error?
43. I have my backup RMAN script called "backup_rman.sh". I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
rman target / catalog rman/rman <<_END_
rman script here.
_END_
shell_script &
44. Explain the concept of the DUAL table.
a table with only one row and one column.
to help show expressions internally in oracle.
45. What are the ways tablespaces can be managed and how do they differ?
dmt dictionary managed tablespace (permenent tablespace/temprary tablespace).
lmt local managed tablespaces.
uniform sized extents
system managed extents
freelist managed space allocation
bitmap managed space allocation
permenent tablespace
temporary tablespace datafile
temporary tablespace tempfile
undo tablespace
46. From the database level， how can you tell under which time zone a database is operating?
select * from nls_database_parameters;
select * from nls_instance_parameters;
47. What's the benefit of "dbms_stats" over "analyze"?
too much, not very clear.
48. Typically， where is the conventional directory structure chosen for Oracle binaries to reside?
$ORACLE_HOME/bin
49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.
alter database create datafile 'file_path' as 'file_path';
recover datafile;
