Common Interview Questions for Oracle Database Administrator.

Here are some of the Common Interview Questions for Oracle Database Administrator. As of now it contains 120 Questions. These questions are common for both Senior Oracle DBA or Junior DBA. I have divided the questions into 3 categories Technical Interview Questions, Unix Related questions asked to an Oracle Database Administrator (DBA) and Backup and Recovery Interview Questions commonly asked in Oracle DBA interview

1. What is an Oracle Instance?
2. What information is stored in Control File?
3. When you start an Oracle DB which file is accessed first?
4. What is the Job of  SMON, PMON processes?
5. What is Instance Recovery?
6. What is written in Redo Log Files?
7. How do you control number of Datafiles one can have in an Oracle database?
8. How many Maximum Datafiles can there be in an Oracle Database?
9. What is a Tablespace?
10. What is the purpose of  Redo Log files?
11. Which default Database roles are created when you create a Database?
12. What is a Checkpoint?
13. Which Process reads data from Datafiles?
14. Which Process writes data in Datafiles?
15. Can you make a Datafile auto extendible. If yes, how?
16. What is a Shared Pool?
17. What is kept in the Database Buffer Cache?
18. How many maximum Redo Logfiles one can have in a Database?
19. What is difference between PFile and SPFile?
20.  What is PGA_AGGREGRATE_TARGET parameter?
21. Large Pool is used for what?
22. What is PCT Increase setting?
23. What is PCTFREE and PCTUSED Setting?
24. What is Row Migration and Row Chaining?
25. What is 01555 - Snapshot Too Old error and how do you avoid it?
26. What is a Locally Managed Tablespace?
27. Can you audit SELECT statements?
28. What does DBMS_FGA package do?
29. What is Cost Based Optimization?
30. How often you should collect statistics for a table?

31. How do you collect statistics for a table, schema and Database?
32. Can you make collection of Statistics for tables automatic?
33. On which columns you should create Indexes?
34. What type of Indexes are available in Oracle?
35. What is B-Tree Index?
36. A table is having few rows, should you create indexes on this table?
37. A Column is having many repeated values which type of index you should create on this column, if you have to?
38. When should you rebuilt indexes?
39. Can you built indexes online?
40. Can you see Execution Plan of a statement.
41. A table is created with the following setting

      storage (initial 200k
                   next 200k
                   minextents 2
                   maxextents 100
                   pctincrease 40)

     What will be size of 4th extent?

42. What is DB Buffer Cache Advisor?

43. What is STATSPACK tool?

44. Can you change SHARED_POOL_SIZE online?

45. Can you Redefine a table Online?

46. Can you assign Priority to users?

47. You want users to change their passwords every 2 months. How do you enforce this?

48. How do you delete duplicate rows in a table?

49. What is Automatic Management of Segment Space setting?

50. What is the difference between DELETE and TRUNCATE statements?

51. What is COMPRESS and CONSISTENT setting in EXPORT utility?

52. What is the difference between Direct Path and Convention Path loading?

53. Can you disable and enable Primary key?

54. What is an Index Organized Table?

55. What is a Global Index and Local Index?

56. What is the difference between Range Partitioning and Hash Partitioning?

57. What is difference between Multithreaded/Shared Server and Dedicated Server?

58. Can you import objects from Oracle ver. 7.3 to 9i?

59. How do you move tables from one tablespace to another tablespace?

60. How do see how much space is used and free in a tablespace?