Oracle DBA (Database Administration) Interview Questions

 
1.
When using Oracle export/import what character set concerns might come up? How do you handle them?
2.
How do you use automatic PGA memory management with Oracle 9i and above?
3.
Explain two easy SQL optimizations.
4.
Name three SQL operations that perform a SORT.
5.
What is your favorite tool for day-to-day Oracle operation?
6.
What is the difference between Truncate and Delete? Why is one faster?
Can we ROLLBACK both? How would a full table scan behave after?
7.
What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?
8.
What does the NO LOGGING option do? Why would we use it? Why would we be careful of using it?
9.
Tell me about standby database? What are some of the configurations of it? What should we watch out for?
10.
What is the difference between RMAN and a traditional hotbackup?
11.
What are bind variables and why are they important?
12.
In PL/SQL, what is bulk binding, and when/how would it help performance?
13.
Why is SQL*Loader direct path so fast?
14.
What are the tradeoffs between many vs few indexes? When would you want to have many, and when would it be better to have fewer?
15.
What is the difference between RAID 5 and RAID 10? Which is better for Oracle?
16.
what is difference between Co-related sub query and nested sub query?
17.
How do I write a cron which will run a SQL query and mail the results to a group?
18.
What operator performs pattern matching
19.
What is the use of the DROP option in the ALTER TABLE command?
20.
Which date function is used to find the difference between two dates?
21.
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
22.
Which function is used to find the largest integer less than or equal to a specific value?
23.
What operator tests column for the absence of data?
24.
What are the steps involved in Database Start up?
25.
What are the different modes of mounting a Database with the Parallel
Server?
26.
What is Oracle Full Backup?
27.
What is Oracle Partial Backup?
28.
What is Oracle Archived Redo Log?
29.
What are the advantages of operating a database in ARCHIVELOG mode
over operating it in NO ARCHIVELOG mode?
30.
What is Oracle Log Switch?
31.
What are the steps involved in Instance Recovery in Oracle?
32.
Do you need a commit after DDL statements?
33.
How would you pass hints to the SQL processor?
34.
what is the difference between group by and order by?
35.
A DBA had to remove some Archivelogs to free up space in filesystem. Now when the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command can be used so it wo not complain about missing Archivelogs.
36.
Which RMAN command is used to create an exact replica of a database in new host?
37.
How do you install Statspack?
38.
Process you follow to start looking into Performance issue at database level (If the application is running very slow, at what points do you need to go about the database in
order to improve the performance?)
39.
Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE?
40.
List five most important parameter in 9i affecting performance?
41.
What is PGA_AGGREGATE_TARGET?
42.
How do you analyze table partition using Oracle provided package?
43.
You see a wait on LMS process in statspack, what does that mean?
44.
Explain procedure to Change CHARACTERSET of a database.
45.
If you had a tablespace, TEST_TABLESPACE, which consists of three files: TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to delete the file TEST02.dbf, what else would you need in order to recover all the data that was present in TEST_TABLESPACE at the time that TEST02.dbf was deleted?
46.
How do you put database is ARCHIVELOG mode, explain procedure?
47.
How can you tell if an index on particular table is USED or NOT USED in 9i?
48.
Explain FORCE LOGGING feature in 9i.
49.
When using Oracle export/import what character set concerns might come up? How do you handle them?
50.
How do you use automatic PGA memory management with Oracle 9i and above?
51.
Explain two easy SQL optimizations.
52.
Name three SQL operations that perform a SORT.
53.
What is your favorite tool for day-to-day Oracle operation?
54.
What is the difference between Truncate and Delete? Why is one faster?
Can we ROLLBACK both? How would a full table scan behave after?
55.
What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?
56.
What does the NO LOGGING option do? Why would we use it? Why would we be careful of using it?
57.
Tell me about standby database? What are some of the configurations of it? What should we watch out for?
58.
What is the difference between RMAN and a traditional hotbackup?
59.
What are bind variables and why are they important?
60.
In PL/SQL, what is bulk binding, and when/how would it help performance?
61.
Why is SQL*Loader direct path so fast?
62.
What are the tradeoffs between many vs few indexes? When would you want to have many, and when would it be better to have fewer?
63.
What is the difference between RAID 5 and RAID 10? Which is better for Oracle?
64.
what is difference between Co-related sub query and nested sub query?
65.
How do I write a cron which will run a SQL query and mail the results to a group?
66.
What operator performs pattern matching
67.
What is the use of the DROP option in the ALTER TABLE command?
68.
Which date function is used to find the difference between two dates?
69.
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
70.
Which function is used to find the largest integer less than or equal to a specific value?
71.
What operator tests column for the absence of data?
72.
What are the steps involved in Database Start up?
73.
What are the different modes of mounting a Database with the Parallel
Server?
74.
What is Oracle Full Backup?
75.
What is Oracle Partial Backup?
76.
What is Oracle Archived Redo Log?
77.
What are the advantages of operating a database in ARCHIVELOG mode
over operating it in NO ARCHIVELOG mode?
78.
What is Oracle Log Switch?
79.
What are the steps involved in Instance Recovery in Oracle?
80.
Do you need a commit after DDL statements?
81.
How would you pass hints to the SQL processor?
82.
what is the difference between group by and order by?
83.
A DBA had to remove some Archivelogs to free up space in filesystem. Now when the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command can be used so it wo not complain about missing Archivelogs.
84.
Which RMAN command is used to create an exact replica of a database in new host?
85.
How do you install Statspack?
86.
Process you follow to start looking into Performance issue at database level (If the application is running very slow, at what points do you need to go about the database in
order to improve the performance?)
87.
Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE?
88.
List five most important parameter in 9i affecting performance?
89.
What is PGA_AGGREGATE_TARGET?
90.
How do you analyze table partition using Oracle provided package?
91.
You see a wait on LMS process in statspack, what does that mean?
92.
Explain procedure to Change CHARACTERSET of a database.
93.
If you had a tablespace, TEST_TABLESPACE, which consists of three files: TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to delete the file TEST02.dbf, what else would you need in order to recover all the data that was present in TEST_TABLESPACE at the time that TEST02.dbf was deleted?
94.
How do you put database is ARCHIVELOG mode, explain procedure?
95.
How can you tell if an index on particular table is USED or NOT USED in 9i?
96.
Explain FORCE LOGGING feature in 9i.
97.
When using Oracle export/import what character set concerns might come up? How do you handle them?
98.
How do you use automatic PGA memory management with Oracle 9i and above?
99.
Explain two easy SQL optimizations.
100.
Name three SQL operations that perform a SORT.
101.
What is your favorite tool for day-to-day Oracle operation?
102.
What is the difference between Truncate and Delete? Why is one faster?
Can we ROLLBACK both? How would a full table scan behave after?
103.
What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?
104.
What does the NO LOGGING option do? Why would we use it? Why would we be careful of using it?
105.
Tell me about standby database? What are some of the configurations of it? What should we watch out for?
106.
What is the difference between RMAN and a traditional hotbackup?
107.
What are bind variables and why are they important?
108.
In PL/SQL, what is bulk binding, and when/how would it help performance?
109.
Why is SQL*Loader direct path so fast?
110.
What are the tradeoffs between many vs few indexes? When would you want to have many, and when would it be better to have fewer?
111.
What is the difference between RAID 5 and RAID 10? Which is better for Oracle?
112.
what is difference between Co-related sub query and nested sub query?
113.
How do I write a cron which will run a SQL query and mail the results to a group?
114.
What operator performs pattern matching
115.
What is the use of the DROP option in the ALTER TABLE command?
116.
Which date function is used to find the difference between two dates?
117.
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
118.
Which function is used to find the largest integer less than or equal to a specific value?
119.
What operator tests column for the absence of data?
120.
What are the steps involved in Database Start up?
121.
What are the different modes of mounting a Database with the Parallel
Server?
122.
What is Oracle Full Backup?
123.
What is Oracle Partial Backup?
124.
What is Oracle Archived Redo Log?
125.
What are the advantages of operating a database in ARCHIVELOG mode
over operating it in NO ARCHIVELOG mode?
126.
What is Oracle Log Switch?
127.
What are the steps involved in Instance Recovery in Oracle?
128.
Do you need a commit after DDL statements?
129.
How would you pass hints to the SQL processor?
130.
what is the difference between group by and order by?
131.
A DBA had to remove some Archivelogs to free up space in filesystem. Now when the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command can be used so it wo not complain about missing Archivelogs.
132.
Which RMAN command is used to create an exact replica of a database in new host?
133.
How do you install Statspack?
134.
Process you follow to start looking into Performance issue at database level (If the application is running very slow, at what points do you need to go about the database in
order to improve the performance?)
135.
Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE?
136.
List five most important parameter in 9i affecting performance?
137.
What is PGA_AGGREGATE_TARGET?
138.
How do you analyze table partition using Oracle provided package?
139.
You see a wait on LMS process in statspack, what does that mean?
140.
Explain procedure to Change CHARACTERSET of a database.
141.
If you had a tablespace, TEST_TABLESPACE, which consists of three files: TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to delete the file TEST02.dbf, what else would you need in order to recover all the data that was present in TEST_TABLESPACE at the time that TEST02.dbf was deleted?
142.
How do you put database is ARCHIVELOG mode, explain procedure?
143.
How can you tell if an index on particular table is USED or NOT USED in 9i?
144.
Explain FORCE LOGGING feature in 9i.
145.
When using Oracle export/import what character set concerns might come up? How do you handle them?
146.
How do you use automatic PGA memory management with Oracle 9i and above?
147.
Explain two easy SQL optimizations.
148.
Name three SQL operations that perform a SORT.
149.
What is your favorite tool for day-to-day Oracle operation?
150.
What is the difference between Truncate and Delete? Why is one faster?
Can we ROLLBACK both? How would a full table scan behave after?
151.
What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?
152.
What does the NO LOGGING option do? Why would we use it? Why would we be careful of using it?
153.
Tell me about standby database? What are some of the configurations of it? What should we watch out for?
154.
What is the difference between RMAN and a traditional hotbackup?
155.
What are bind variables and why are they important?
156.
In PL/SQL, what is bulk binding, and when/how would it help performance?
157.
Why is SQL*Loader direct path so fast?
158.
What are the tradeoffs between many vs few indexes? When would you want to have many, and when would it be better to have fewer?
159.
What is the difference between RAID 5 and RAID 10? Which is better for Oracle?
160.
what is difference between Co-related sub query and nested sub query?
161.
How do I write a cron which will run a SQL query and mail the results to a group?
162.
What operator performs pattern matching
163.
What is the use of the DROP option in the ALTER TABLE command?
164.
Which date function is used to find the difference between two dates?
165.
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
166.
Which function is used to find the largest integer less than or equal to a specific value?
167.
What operator tests column for the absence of data?
168.
What are the steps involved in Database Start up?
169.
What are the different modes of mounting a Database with the Parallel
Server?
170.
What is Oracle Full Backup?
171.
What is Oracle Partial Backup?
172.
What is Oracle Archived Redo Log?
173.
What are the advantages of operating a database in ARCHIVELOG mode
over operating it in NO ARCHIVELOG mode?
174.
What is Oracle Log Switch?
175.
What are the steps involved in Instance Recovery in Oracle?
176.
Do you need a commit after DDL statements?
177.
How would you pass hints to the SQL processor?
178.
what is the difference between group by and order by?
179.
A DBA had to remove some Archivelogs to free up space in filesystem. Now when the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command can be used so it wo not complain about missing Archivelogs.
180.
Which RMAN command is used to create an exact replica of a database in new host?
181.
How do you install Statspack?
182.
Process you follow to start looking into Performance issue at database level (If the application is running very slow, at what points do you need to go about the database in
order to improve the performance?)
183.
Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE?
184.
List five most important parameter in 9i affecting performance?
185.
What is PGA_AGGREGATE_TARGET?
186.
How do you analyze table partition using Oracle provided package?
187.
You see a wait on LMS process in statspack, what does that mean?
188.
Explain procedure to Change CHARACTERSET of a database.
189.
If you had a tablespace, TEST_TABLESPACE, which consists of three files: TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to delete the file TEST02.dbf, what else would you need in order to recover all the data that was present in TEST_TABLESPACE at the time that TEST02.dbf was deleted?
190.
How do you put database is ARCHIVELOG mode, explain procedure?
191.
How can you tell if an index on particular table is USED or NOT USED in 9i?
192.
Explain FORCE LOGGING feature in 9i.