Oracle DBA Interview Questions By B G 1. How many memory layers are in the shared pool?
+ ~" e' x! l0 ~; Z7 M 2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
* a V. { j- A) A# M" a 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?- i8 ^4 T5 o# Z* {# I% m8 f: j$ t
4. Define the SGA and:" h/ S% w- U# J! v$ H
? How you would configure SGA for a mid-sized OLTP environment?5 c6 k5 L6 u( }! w
? What is involved in tuning the SGA?+ N5 o; \" R0 J- j' {
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?
* u. g' e9 K8 s: q 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 C3 ]; D8 V( E, O. L2 W* a1 S9 F
7. How do you tell what your machine name is and what is its IP address?: s, I3 W% |, L0 B: q
8. How would you go about verifying the network name that the local_listener is currently using?
" W; X( r! g1 o& L 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?
# l7 G3 ^' v1 a* G% z1 _ 10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?% y, d7 a) R& D( A0 l. ~6 \
11. What is the recommended interval at which to run statspack snapshots, and why?" ^0 L d2 W7 c f
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?( o7 u |& [; p) b
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.
' l& P2 X4 L( u, Y 14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
" r N+ n8 z( C5 } N8 Z% x, d 15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
/ P Q. ~+ L Q" I 16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
# S6 l, ~. T! e5 P 17. How would you best determine why your MVIEW couldn't FAST REFRESH?1 b* j0 q" ^! i' a- a L
18. How does propagation differ between Advanced Replication and Snapshot Replication (readonly)?5 z& S. J. s' i; n4 W) _
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?0 k/ h& p2 v0 e/ P8 l# b
20. How would you begin to troubleshoot an ORA-3113 error?
. v1 v$ H y. ?% p9 D7 W) y+ K 21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
; H& k6 }- l5 M: u 22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed", how do you approach troubleshooting this issue?
4 y% Q$ g* ~& F1 {9 K* n 23. How would you extract DDL of a table without using a GUI tool?
# t% B4 N$ ]- w* X/ W 24. You're getting high "busy buffer waits" - how can you find what's causing it?
* m0 [" @' H1 K8 i1 a: q 25. What query tells you how much space a tablespace named "test" is taking up, and how much space is remaining? |