Whether you’re just starting out on your Oracle DBA career or you’re an experienced DBA looking to switch jobs, you’re going to have to face interviews where you’ll be asked technical DBA questions as well as the usual “Tell us about yourself” type interview questions.
List Of Oracle DBA Interview Questions And Answers
To help you out, we’ve collated a list of typical questions you’re likely to be asked at a DBA interview along with good answers.
Don’t be put off by the length of the list – you’re unlikely to be asked every question! We’ve just tried to compile as comprehensive a list of possible Oracle interview questions as possible, that’s all. And we’re likely to add to the list in future as we think of more questions, so make sure to check back at regular intervals.
Oracle Interview Questions And Answers
1. Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is taken while the database is up and running. No downtime is required.
A cold backup requires downtime but the backup and recovery procedure is easier. There is also a slight performance improvement for the database because it doesn’t have to run in archivelog mode.
2. What are the four different shutdown modes for the Oracle database and what’s the difference between them?
SHUTDOWN NORMAL – new connections are not allowed, waits for all users to disconnect from the database. No instance recovery is needed on next start up.
SHUTDOWN TRANSACTIONAL – no new transactions are allowed to start, waits for active transactions to finish. After all transactions are completed, any users still connected are disconnected. No instance recovery is needed on next start up.
SHUTDOWN IMMEDIATE – doesn’t wait for current calls to complete or users to disconnect from the database. Rolls back any uncommitted transactions. No instance recovery is needed on next start up.
SHUTDOWN ABORT – fastest possible database shutdown. Uncommitted transactions not rolled back. Any active statements are terminated and all users disconnected from the database. Next start up will require recovery.
3. Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage in a database. An extent is a contiguous collection of data blocks. All the extents that an object uses when grouped together are the segment for the object.
4. Where would you look for errors from the database engine?
In the database alert log.
5. What is the difference between a database and an instance, and what is the relation between the two?
See this article: Oracle Training – What Is an Oracle Database?
6. Compare and contrast TRUNCATE and DELETE for a table.
DELETE is a data manipulation language (DML) command, whereas TRUNCATE is a data definition language (DDL) command. As DELETE is a DML command, deletes can be rolled back whereas truncate commands can’t. TRUNCATE is much faster than DELETE and resets the table high water mark. You can’t use WHERE clauses with a TRUNCATE command whereas you can with a DELETE command. Triggers aren’t fired with a TRUNCATE command but they are with a DELETE.
7. What are the components of the SGA?
The SGA is the Shared Global Area. It’s main components are the library cache, the data dictionary cache, the database buffer cache, the redo log buffer cache and the shared pool.
8. What’s the difference between undo and redo?
Undo is a record of how to undo a change. It’s used for read consistency and rollback and is stored in undo segments.
Redo is a record of how to reproduce a change. It’s used for rolling forward database changes and is stored in the redo log files.
9. On a Unix server, where is the listener.ora file usually stored?
$ORACLE_HOME/network/admin
10. What is a foreign key?
The child entry in a table that points to the parent. Referential integrity ensures that all child records have a parent.
11. What is a materialized view?
A database object that contains the results of a query. They are often used to cache expensive queries in data warehouses.
12. When would you use a fast refresh of a materialized view and when would you do a complete refresh?
If many rows in the underlying table have been modified a complete refresh is best. If only a subset of the data has been changed then a fast refresh is appropriate.
13. What is a deadlock?
When two sessions are each waiting for the other to complete a transaction before they can continue. Oracle rolls back one of the transactions with an ORA-00060 error.
14. How can you restrict resource usage for a group of users?
By using profiles.
15. During a hot backup what critical step must be performed before copying the datafiles belonging to a tablespace?
ALTER TABLESPACE … BEGIN BACKUP;
16. How can you check the archivelog mode of the database?
select log_mode from v$database;
or
connect as sysdba and: archive log list;
17. What is a “Snapshot Too Old” error?
The data needed for a user’s long running transaction is no longer available because that area of undo has been over-written with new data.
18. How does Oracle maintain read consistency?
Oracle keeps multiple versions of a data block as it is being changed by different users.
19. Why would you almost always avoid bitmap indexes on an OLTP system?
Because bitmap indexes can lock many rows at a time, causing severe locking problems.
20. What does an “alter database open resetlogs” do?
Oracle re-initializes the redo log so that no further recovery is possible. If you are applying archive logs to roll forward the database and issue this command, no further archive logs can be applied.
21. How can you change the number of data blocks read at a time?
By changing the db_file_multiblock_read_count parameter.
22. Which data dictionary views would you query to identify the statements and transactions currently running in the database right now?
v$sql and v$session
23. How can you easily view the status of a long running table scan?
Query the v$session_longops data dictionary view.
24. What is index monitoring?
A monitoring facility within Oracle that can be used to identify if indexes are actually being used.
25. When does Oracle issue an implicit commit?
Every time you execute a DDL statement.
26. What is an autonomous transaction?
A separate transaction that commits or rolls back independently of others in a job. Normally you’d want all transactions in a job to commit at the same time, at the end of the job.
27. What’s the difference between sequential and scattered reads?
A sequential read is a single-block read. A scattered read is a multi-block read.
28. What does Oracle do if there is a syntax error in a SQL hint?
It ignores the hint.
29. What are stored outlines and why would you use them?
A stored outline is a collection of hints associated with a SQL statement that allows a standard execution plan to be maintained regardless of changes in the system environment or associated statistics. The idea is to maintain an execution plan from a point in time when performance was considered acceptable.
30. Why would you use an index?
For faster access to the required data blocks in a table.
31. Which background process performs instance recovery?
The SMON process.
Hopefully, these DBA interview questions and answers will help you to thoroughly prepare for your interview, and clinch that elusive job offer.