Oracle DBA Interview Questions By B G 1. How many memory layers are in the shared pool?
+ ^) j1 F' {- h& g 2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?3 _# t; a1 f- t( _# ?0 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?
) J" \+ n {/ V9 J 4. Define the SGA and:
: p! J1 R' f" r ? How you would configure SGA for a mid-sized OLTP environment?0 l: b9 g$ O) n0 a' s; n
? What is involved in tuning the SGA?# m8 O0 o: g1 O- 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?
; J6 C/ i# X6 b5 w 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?& p& J& o# o3 ] Y* ?2 [3 r& {# k
7. How do you tell what your machine name is and what is its IP address?% }. [9 |, B' t3 a) ]5 O9 A
8. How would you go about verifying the network name that the local_listener is currently using?
% I5 Q" ?" I! S( q 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?- v0 {0 T+ Q1 i: o
10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?; F8 i( A2 {0 M
11. What is the recommended interval at which to run statspack snapshots, and why?
: J% J/ c5 {; v3 }2 a) m 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?" O+ e; r- k* z4 ~
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.0 B Q. f5 Y- q. G
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?! q, U( @( Z! G' F/ ^8 w) \& |
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
0 E O t* A* G6 Q8 d7 E 16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
" Z5 j- {8 t* V. I Q5 F: @* H 17. How would you best determine why your MVIEW couldn't FAST REFRESH?8 b% N( N! }3 \
18. How does propagation differ between Advanced Replication and Snapshot Replication (readonly)?0 t# j0 o" M9 o6 N5 i
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 p9 d/ Z1 R# W) b1 z* G) |) K
20. How would you begin to troubleshoot an ORA-3113 error?
: y/ J7 D1 _1 y5 M% b8 n7 w$ x$ z R 21. Which dictionary tables and/or views would you look at to diagnose a locking issue?- D; {6 j( b% I+ f( i
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed", how do you approach troubleshooting this issue?- i- v* Y( l- P' Z$ \, l) W
23. How would you extract DDL of a table without using a GUI tool?7 C, x3 A4 e) ]7 ?+ a' J9 |
24. You're getting high "busy buffer waits" - how can you find what's causing it?
, W7 W0 W! Y2 n) P9 u6 v. o( ] 25. What query tells you how much space a tablespace named "test" is taking up, and how much space is remaining? |