Oracle DBA Interview Questions By B G 1. How many memory layers are in the shared pool?! }4 s' U: M+ D0 U' T! j
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
& P5 X) w, F9 q5 Z+ K! T 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?* w8 _+ b, O7 R+ J, X/ I$ \
4. Define the SGA and:. Q, G- [' E9 |$ \ [6 g1 M
? How you would configure SGA for a mid-sized OLTP environment?
% g9 t% G" I X+ H ? What is involved in tuning the SGA?
2 O+ L0 E5 I f8 C) _ 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?2 ]" g& p( D* u# }0 o+ Z
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?
& ?) F$ b" @& T' ]* v6 y, z {- L9 H+ G 7. How do you tell what your machine name is and what is its IP address?) K0 k [! q3 J* Z1 B
8. How would you go about verifying the network name that the local_listener is currently using?9 l. F# W0 J& f
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?
: {, E$ f. _+ c2 W- N ~1 ]% {4 b( A6 J 10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?
3 Y. n7 e7 N0 ^" O y7 X, [ 11. What is the recommended interval at which to run statspack snapshots, and why?6 M8 m$ ?+ B/ h, ?
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?
& x0 X2 X3 u& x0 j$ t7 | 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.9 C7 r% O- |: r( N" o0 b& d
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?* \% x9 Z$ h; y
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
% S7 b1 `" a( \" G- ^4 E" N 16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
( v. M7 y* L- L7 a* i. D- m* M 17. How would you best determine why your MVIEW couldn't FAST REFRESH?7 m- s1 Z9 ~7 n- o7 [; T
18. How does propagation differ between Advanced Replication and Snapshot Replication (readonly)?: _5 y* J7 y# N7 K+ L7 p* P
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
W7 b( t5 y- a 20. How would you begin to troubleshoot an ORA-3113 error?" ]) `! m; m: i; J, \2 ~. A
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?6 `* j1 Z a e+ T
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed", how do you approach troubleshooting this issue?+ Y9 c C; a3 } |) h2 F; I9 \6 P
23. How would you extract DDL of a table without using a GUI tool?
7 B# h7 U: r5 R# `$ L( b 24. You're getting high "busy buffer waits" - how can you find what's causing it?5 s6 C1 A( T! f- Q9 w; n
25. What query tells you how much space a tablespace named "test" is taking up, and how much space is remaining? |