Oracle DBA Interview Questions By B G
7 P. ]+ K, b7 J5 i, Z `2 U- s 1. How many memory layers are in the shared pool?
; `: ]/ @& l' Z5 A: ]4 Q- ` 2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
; d" o# \* U7 U8 u/ P 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? w* k* x0 \3 U: u
4. Define the SGA and:) y% |; e# {3 W6 Q7 Y9 e6 c
? How you would configure SGA for a mid-sized OLTP environment?
+ n6 ^, r9 B6 l" q+ z: ~ ? What is involved in tuning the SGA?" S6 [- v5 D) X6 r$ J4 g
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?
. ~; {" m' p6 K3 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?
9 I8 i' \" H# x( O3 ^* u 7. How do you tell what your machine name is and what is its IP address?$ j1 Y- c. F2 ?/ q7 `: D0 ~1 n8 {
8. How would you go about verifying the network name that the local_listener is currently using?
8 D; R2 ~ N; ?* D 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?
' m1 B6 t& B: | F( K 10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?! V7 k5 y Q5 Z9 W
11. What is the recommended interval at which to run statspack snapshots, and why?
$ a2 R9 Z3 }( H3 Y 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?5 @6 l6 n8 @! o& e- e1 X# A* N9 p
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* t A/ l N7 Q 14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
6 G4 c4 g* ~+ Q; P 15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?; Q# O1 ^( B4 u0 g w6 ^
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
: m* `& ^) X# o: S/ [2 Z2 s 17. How would you best determine why your MVIEW couldn't FAST REFRESH?" h" u% g' q! l7 V9 O
18. How does propagation differ between Advanced Replication and Snapshot Replication (readonly)?
. V! }, |4 \3 `, b 19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?- I: F% x. j7 i/ z: M s
20. How would you begin to troubleshoot an ORA-3113 error?
6 C. H* G6 s1 w 21. Which dictionary tables and/or views would you look at to diagnose a locking issue?! P' Z- i4 T) b( s7 H! l
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed", how do you approach troubleshooting this issue?! c' P, z& R k* X
23. How would you extract DDL of a table without using a GUI tool?5 y: ~- A) n1 j2 {6 J
24. You're getting high "busy buffer waits" - how can you find what's causing it?1 N. P* C3 K* f3 R* {7 k
25. What query tells you how much space a tablespace named "test" is taking up, and how much space is remaining? |