10 Most Likely Asked ORACLE DBA Interview Questions And Answers

$98,122 per year is the average salary of a Database Administrator in the United States, says Indeed.com.

An entry-level Oracle Database Administrator (DBA) with less than 1 year of experience can expect to earn an average total compensation (including tips, bonus, and overtime pay) of ₹280,188. An early career Oracle Database Administrator (DBA) with 1-4 years of experience earns an average total compensation of ₹426,638 (Source: Payscale.com)

A lucrative career like this makes the job interviews difficult to crash. Take help from these top 10 Oracle DBA Interview Questions highly asked in Administrator job interviews.

1: You as a DBA just gathered the statistics on schema A. Schema A has 1500 tables. You want to know the name of the table with the highest number of records without running a count on each. How do you do this?

2: At what stages does an instance pass while starting up?

3: How does Oracle execute scheduler jobs and which background processes take part in this?

4: What is a materialized view and how does it differ from a regular view?

5: What parts can be found in a regular SELECT statement?

6: From the isolation levels, explain how “serializable” transactions behave?

7: What are the advantages of using ASM (Automatic Storage Management) over a regular file system?

8: Can you have ASM configured with RAC? What are the benefits of having ASM configured?

9: What are the three types of data block buffer states?

10: Explain the Shared Server Architecture in Oracle Networking.

Let’s discuss the answers to these questions.

1: You as a DBA just gathered the statistics on schema A. Schema A has 1500 tables. You want to know the name of the table with the highest number of records without running a count on each. How do you do this?

Answer:

You query the NUM_ROWS column in the DBA_TABLES table. After running the statistics, this field is populated with current and updated data, and it is a simple and quick method for getting this information without going to every table and counting the records.

2: At what stages does an instance pass while starting up?

Answer:

You can start up a database with the modes below:

a) NOMOUNT: This is the first stage. At this mode the instance is started.

b) MOUNT: This is the second stage. At this mode, the instance is started and the database is mounted. However, the database is not open so you cannot still access data. However you can perform several maintenance tasks at this stage.

c) OPEN: This is the final stage. The database is open and all the data is accessible. The default open mode is “read/write” which means you can read data or write to it. However, it is also possible to open it in “read-only” mode where you can only read data but cannot change it.

3: How does Oracle execute scheduler jobs and which background processes take part in this?

Answer:

The scheduled jobs are managed by “Oracle Scheduler”. The information about the scheduled jobs is stored in the JOB$ table. The “Oracle Scheduler” spawns a Job Coordinator (CJQ0) process to run the jobs. This process is the coordinator. It will monitor the JOB$ table to see if there is any job waiting to be run. The coordinator will then spawn “job queue slave processes” named “Jnnn” to execute the scheduled tasks. These background processes work dynamically. They may enter a sleep state when they are idle or they might completely shut down if they remain idle for a certain time. They will be re-spawned later when needed in the future.

4: What is a materialized view and how does it differ from a regular view?

Answer:

A regular view is only a definition of a SELECT query. It only contains the SQL text in the data dictionary. It has no segment and it doesn’t store any data. A materialized view also has a definition which is still an SQL query but this time the materialized view has a segment and the result of the SQL query is stored in that segment. When a user queries a regular view, he is directed to the underlying tables to retrieve data. However, when a user queries a materialized view, he directly retrieves the data in the Materialized view segment. The user doesn’t read the base tables.

5: What parts can be found in a regular SELECT statement?

Answer:

a) Projection: This is the part where we define a set of columns to read from the table. This means that all the columns will be selected.

b) FROM: This is the part where define a set of tables or views which want to read data from.

c) Predicate: This is the part where we do filtering. This is also called the “where” clause.

d) Sorting: This is the part where you can sort the result according to certain columns.

e) Grouping: This is the part where you can group the results according to certain columns.  

6: From the isolation levels, explain how “serializable” transactions behave.

Answer:

A transaction starts implicitly when a DML statement is executed in a session. Multiple statements might be executed until the transaction ends.

In a “serializable” transaction, all the SELECT queries will return rows as they were at the time the transaction began. Not the time the SELECT query began.

You can still make modifications to data in a serializable transaction, but this data should not have been changed by another transaction after the current transaction began.

Otherwise you’ll get an error.

7: What are the advantages of using ASM (Automatic Storage Management) over a regular file system?

Answer:

When you use ASM, you can make direct I/O to the underlying disks. If you use a file system, there will be an overhead of using the file system. You have to use the interfaces of that file system.

The maintenance is easier in ASM. ASM will manage the disks itself. In a file system you need to manage the disks yourself. ASM acts like a volume manager. You can dynamically add disks to it or remove disks from it. Regular file systems don’t have these capabilities. You have to use a separate volume manager.

ASM distributes the files to each underlying disk and because of that it provides a better I/O performance.

8: Can you have ASM configured with RAC? What are the benefits of having ASM configured?

Answer:

Yes you can. They are complementing technologies. ASM provides a storage abstraction level by grouping several disks into one Diskgroup. This simplifies the space administration and allows the creation of failover and mirrored groups,increasing DB reliability.

9: What are the three types of data block buffer states?

Answer:

A data block in the database buffer cache can be in three states.

These are:

a) Unused: This block has been created during buffer cache initiation but has never been used.

b) Dirty: A data block was read from the disk into the cache and it was updated by a server process. The block on the cache is different from the corresponding block on the disk.

c) Clean: The data block in the buffer cache is the same as the corresponding data block on the disk.

10: Explain the Shared Server Architecture in Oracle Networking.

Answer:

A connection is established between a server process and a client process. The server process executes the incoming requests and sends back the results to client processes. In dedicated server architecture, the server process is exclusive to that client process. In shared server architecture, on server process can serve multiple client requests. The dispatcher processes will accept the request from clients, they will deliver the request to shared server processes for executing and then they will get the results from shared server processes and send them back to the client process which has requested it.

To ace other aspects of your interview, refer to our Job Interview Questions Book Series, which includes books such as HR Interview Questions You’ll Most Likely Be Asked (Third Edition) and Innovative Interview Questions You’ll Most Likely Be Asked.

All the best!