Oracle DBA Interview Preparation Guide
Elevate your Oracle DBA interview readiness with our detailed compilation of 50 questions. Each question is designed to test and expand your Oracle DBA expertise. Suitable for all experience levels, these questions will help you prepare thoroughly. Download the free PDF now to get all 50 questions and ensure youre well-prepared for your Oracle DBA interview. This resource is perfect for in-depth preparation and boosting your confidence.50 Oracle DBA Questions and Answers:
1 :: When using Oracle export/import what character set concerns might come up? How do you handle them?
Be sure to set NLS_LANG for example to "AMERCIAN_AMERICA.WE8ISO8859P1". If your source database is US7ASCII, beware of 8-bit characters. Also be wary of multi-byte characters sets as those may require extra attention. Also watch export/import for messages about any "character set conversions" which may occur.
2 :: How do you use automatic PGA memory management with Oracle 9i and above?
Set the WORKAREA_SIZE_POLICY parameter to AUTO and set PGA_AGGREGATE_TARGET
3 :: Explain two easy SQL optimizations.
a. EXISTS can be better than IN under various conditions
b. UNION ALL is faster than UNION (not sorting)
b. UNION ALL is faster than UNION (not sorting)
4 :: Name three SQL operations that perform a SORT.
a. CREATE INDEX
b. DISTINCT
c. GROUP BY
d. ORDER BY
f. INTERSECT
g. MINUS
h. UNION
i. UNINDEXED TABLE JOIN
b. DISTINCT
c. GROUP BY
d. ORDER BY
f. INTERSECT
g. MINUS
h. UNION
i. UNINDEXED TABLE JOIN
5 :: What is your favorite tool for day-to-day Oracle operation?
Hopefully we hear some use of command line as the answer!
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?
Truncate is nearly instantaenous, cannot be rolled back, and is fast because Oracle simply resets the HWM. When a full table scan is performed on a table, such as for a sort operation, Oracle reads to the HWM. So if you delete every single solitary row in 10 million row table so it is now empty, sorting on that table of 0 rows would still be extremely slow.
7 :: What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?
Fast refresh maintains a change log table, which records change vectors, not unlike how the redo logs work. There is overhead to this, as with a table that has a LOT of indexes on it, and inserts and updates will be slower. However if you are performing refreshes often, like every few minutes, you want to do fast refresh so you don't have to full-table-scan the source table. Complete refresh is good if you're going to refresh once a day. Does a full table scan on the source table, and recreats the snapshot/mview. Also inserts/updates on the source table are NOT impacted on tables where complete refresh snapshots have been created.
8 :: What does the NO LOGGING option do? Why would we use it? Why would we be careful of using it?
It disables the logging of changes to the redologs. It does not disable ALL LOGGING, however as Oracle continues to use a base of changes, for recovery if you pull the plug on the box, for instance. However it will cause problems if you are using standby database. Use it to speed up operations, like an index rebuild, or partition maintenance operations.
9 :: Tell me about standby database? What are some of the configurations of it? What should we watch out for?
Standby databases allow us to create a copy of our production db, for disaster recovery. We merely switch mode on the target db, and bring it up as read/write. Can setup as master->slave or master->master. The latter allows the former prod db to become the standby, once the failure cause is remedied. Watch out for NO LOGGING!! Be sure we're in archivelog mode.
10 :: What is the difference between RMAN and a traditional hotbackup?
RMAN is faster, can do incremental (changes only) backups, and does not place tablespaces into hotbackup mode.