Oracle DBA Interview Questions & Answers

Oracle DBA Interview Questions and Answers:

1. How does a deadlock arise? How to avoid it or overcome it?

The deadlock is a race situation where two or more processes are brought down waiting in their attempt to lock a specific row. This case occurs due to the poor design of the database access or front end application. This is avoided by creating the specific lock mechanisms to be atomic in nature or used in same sequential order everywhere. Once deadlock occurs there is huge impact on the server’s performance and it is taken off only by commit/rollback of the database or by manual termination of the processes in deadlock.

2. What is a look-up table?

A look-up table is a table with data that is updated very rarely. This table is more of a static valued entity that is cross referenced against some indexed values (more like a hash). The other processes refer this table to get some info now and then.

3. When are hashed clusters suitable to be created?

The hash clusters are usually created for a table with less frequently added/modified data. Usually the number of key values and total number of input that we are dealing with is predictable and does not exceed a predefined high value. This can come in handy when most of the queries accessing the hashed values are using equality comparisons.

4. What is an index?

Oracle creates an index for every unique row that is entered into the database. This is the one that can be used directly to access any specific row in any table in the database. This is created by the system whenever a new row is inserted into any table.

5. What is row chaining?

Row chaining refers to the concept of extending a row data into multiple rows, when a single empty row cannot hold the entire data due to less block size values.

6. What is data segment?

A data segment refers to the set of extents that the oracle system allocates to the specific logical data containers.For example, table is a logical storage structure and there is a data segment that is allocated for the table entity as a single unit before allocation and allocates appropriate data segments when clusters are created.

7. What is a network database link?

You can connect the different systems with their own databases into a network. When oracle name servers are given the network names of the different servers, it automatically generates the links for each of the different databases in the different servers. This global name is referred to as the oracle network database link.

8. What are table clusters?

The tables which share a specific column among them are grouped together with the point of join as that single field that they share. This is referred to as cluster. Cluster can be used for efficient retrieval of data from the database.

9. How are synonyms helpful?

The synonyms offer the abstraction to the database users. The user need not worry about the real source of the data or network aliases that are used at different times. The queries can thus be made constant at all time and more meaningful. The security of the accessed objects are also enhanced when we use synonyms for the accesses. This way no one needs to know about the real owner or real name of a specific data entity.

10. What is a database instance?

The database instance is the set of the memory structure and the background processes that are running at a specific time. The database instance should be created to have the access to the specific database and initially the processes are started in the no mount phase. This can help us in increasing the database performance.

11. What are temporary segments?

The temporary segments are temporary memory regions that are created by the Oracle when an sql query execution requires additional space for processing. This temporary segment is then destroyed after the query execution is completed.

12. What is the difference between DDL and DML?

DDL refers to the commands that defines the database. This included create, drop, truncate etc. The DML commands include the insert, delete, update etc. which help in the modification of just the values in the database. The DDL is the one that defines the Schema of the entire database and DML are the ones that are used for value modifications to the values in the database fields.

13. What is the difference between hot backup and cold backup?

The hot backup is the process of backing up the database while the system is still actively used by users. This requires the database to be running in the archive log mode. This can create quite a constraint on the performance when there are lot of users and a huge database. The cold backup is one that is taken when the system is shut down. The cold back up is an easier and simpler way to backup/restore the database. The system need not be in the archive log mode too. But the availability of system is compromised when we are aiming for cold backups.

14. How are hints passed to the SQL engine?

The hints are passed through the comment lines with a + sign to begin with.

15. What is buffer busy wait? How can it be avoided?

It is the condition in which the server is made to wait for the segment header for the specific table on which concurrent insert and delete has to be done. The segment header has the link for the list of free blocks for that table. This can be increased by the changing the freelist_group parameter appropriately. This will help us avoid the buffer busy wait situation.

16. How can you try increasing the buffer cache hit ratio?

Getting the info from the $db_cache_advice after using the buffer cache advisory will give the appropriate info as to whether a change in the buffer cache size is required.

17. What does the error ORA01555 signify?

This is the error which is obtained when the rollback sequence has encountered a very old snapshot that has to be restored. There may be some logical error and the undo retention parameters can be set to overcome this error.

18. What is the difference between fact tables and measurement tables?

The fact tables contain the real values that are going to be used as metrics. The dimension table are the one that classify and categorize the facts and help us to infer more info on the overall schema related scenarios.

19. What are parallel servers?

Multiple instances of the Oracle server accessing the same database at different places are referred to as parallel servers. The parallel servers implementation is possible in multiple CPU processors and they can greatly increase the performance characteristics of the overall system regarding specific queries and complex ones that are involving access to multiple tables for processing.

20. Can views be based on other views?

Yes the views can be created from other views by directing a SELECT query to use the other view’s data.

21. What is a schema?

Schema refers to the overall organization of data into the multiple data tables. The relationship between all these tables and the interaction between the different tables to get the final inferential output etc are brought out from the schema through the relationship mapping across the various tables and various fields.

22. What are the different locks that you can have over a table?

The different types of LOCKs are shared, shared exclusive and exclusive. These have the dependency over the other entity trying to lock the table too (in case of shared and shared exclusive).

Describe the following:

a) Oracle Archive log

b) Oracle catalog

a) Oracle Archive logs are records which help in putting up the database in event of unforeseen failure or in an event that there is a general need to perform log mining, it is important as it improves the system availability thus improving dependability, one of the features necessary to win a user confidence in system engineering, as it is clear that without this, users will often reject the system in question.

This also helps to save the organization using the system as sometimes data is usually very expensive, even more expensive than the computer system on which it is being handled and the abrupt loss may mean loss of money used in research as well as the time thus having a fall back log is very handy.

In summary, logs are simply captured and stored records.

b) Oracle catalog: These are catalogs comprising of tables and views which helps users to obtain information concerning the entire database such as the metadata as well as other tables and views also available in the database, their attributes and constraints. In these systems (the oracle systems), the available system relation catalog are usually so many that an average user just has an access to a few of them, I mean the available systems relation catalogs runs to the tunes of several hundreds.

Describe PCT Free.

This is a block of storage used to determine the amount of space necessary for future updates in the database so that it is left blank; it is done by a process called mentioning the PCTFREE. This is conspicuous in a situation where one record, say, Isaac can in future is updated to Isaac Kumar.

If for instance PCTFREE has been mentioned as 10, then oracle will have to add new rows to the block thus it allows 10 for future updates.

Describe the difference between hot back up and cold backup as well as the benefits associated with each of them.

a) Hot backup: this is the type of backup which takes place while the system is up and running, usually in the archive log mode.

Benefits of the hot backup: it is good owing that one can recover the database at any instance as this kind of backup takes place when the database is still running and up hence higher chances of capturing all instances which occur in the database at any point in time.

Also this system is convenient as the database is still available to the user to access even during the time of update.

b) Cold Backup: This is the type of backup which occurs while the database is shut down and hence does not require being in the archive log mode. It has an advantage in that it is easy to administer, both the backup and the recovery processes. Also the fact that you don’t need to put the database in the archive log mode during the backup is an advantage in its own as during such a situation, usually there is a performance gain since the database is cutting no archive logs to disk

Explain how you would go about to bring up a database in a situation whereby you are restoring from a backup and have no control files at all.

The following would be the recommended procedure:

• Create a text based backup control file whereby you stipulate the location of all data files in the disk.
• Issue a recover command using the control file clause.

Describe the term, ‘Dump Destination’; also explain in details the meaning of bdump, cdump and udump.

The term dump destination refers to the location in harboring the trace files for all the oracle processes.

adump--> for ASM processes

udump--> User Processes dump

bdump-->Background processes + alert_SID.log file location

cdump--> Core Processes dump

Useful information pertinent to system failures are contained in these destinations

Name the four performance bottlenecks that are likely to be found in a database server.

• Central processing unit (CPU) bottlenecks
• Incorrect memory structures, also known as the undersized memory structures
• Poor SQL statements,
• Poor database settings

Name the four errors that one can find in an alert (error) log which are capable of ruining the appetite of the DBA and also state how one can avoid them.

• Oracle internal errors
• Backup and recovery errors
• Snapshot to old errors
• Deadlock Errors

Is it possible for one to perform DML operations when the standby database is in read only in Standby Database?

There exist two kinds of standby, namely the logical standby and the physical standby.

Physical standby resembles primary database in terms of the structure.

During read only mode, it is possible to run SQL queries on it, during the other mode, i.e. the redo-apply mode, the database is not accessible to the queries.

Sometimes the logical standby has a different structure from that of the primary database and while in read- only mode, the SQL statements which are normally generated from redo are applied and there exist a possibility for queries to run concurrently.

During the other mode, i.e. the read-write mode, one is in a position to modify data in tables created in addition to the primary schema. It is worth noting that such settings can be overwritten by specifying further security options.

Explain what you will do in an event that a situation arises whereby an error occurs which you are not able to troubleshoot yet there are no seniors or colleagues around.

First, it will be important to find out the particular point in compilation where the error is taking place, then divide the code in to segments, check the correctness of the code line by line in the segment containing the error, actually debug the code, until we find where the problem is arising from and then try and take an appropriate action based on the characteristics of the problem in the code.

Assuming you are an Oracle Database Administrator (DBA), name the whole UNIX file you need to understand.

To check the process I would use
ps -ef |grep pmon
ps -ef

To check the alert log file I would use
tail -f alert.log 

To check the cpu usage, I would use
top
vmstat 25

How to Convert local management tablespace into a dictionary managed tablespace and vice versa?

To convert dictionary managed tablespace into a local management tablespace, one is expected to do the following:

Enter the command:

exec dbms_space_admin.Tablespace_Migrate_to_Local('tablespace');

But to convert the local to dictionary managed tablespace, the following becomes the procedure:

Enter the command:

exec dbms_space_admin.Tablespace_Migrate_FROM_Local('tablespace');

State the different scenarios that lead to rebuilding of indexes.

Essentially, there are two scenarios to consider:

• Deleted entries represent more then twenty percent of the current entries.
• The depth of the index has grown more than four levels

It is wise noting that oracle index nodes never get physically deleted when the rows of tables are deleted, neither are entries removed from the index yet oracle does the deletion logically leaving behind the so called ‘dead nodes’ in the index tree and these deleted nodes may be handy in event that there is need for another adjacent entry. When large number of tables has been deleted, it is possible that that deleted leaf nodes will make the index full scans for longer periods thus disadvantageous.

What should be checked in an event that a database hangs when the developer is attempting to compile the package?

Here, what one checks is the bdump destination.

What to do when rectifying a situation a certain rollback segment gets corrupted?

In such a situation, one restores and then recovers the corrupted rollback segment.

State one of the ways of transferring a table between different schemas.

Out of the many available methods, one may use the following:

1) Export-Import

2) Create table…as SELECT or COPY

State the importance of the IMPORT IGNORE Option, also state its default setting.

This option tells the import to ignore the errors which already exist in the system. By default, the already existing tables will be skipped, else the error is ignored and the table’s data inserted, usually it has a default value of N.

State what happens in event that the DEFAULT and TEMPORARY tablespace clauses aren’t included in the CREATE USER command.

In such a situation, the user is assigned the SYSTEM tablespace which is the default and a temporary table space, this isn’t advisable since sends the user object and temporary segments to the SYSTEM tablespace leading to fragmentation and poor table placement since only objects for the data dictionary and system rollback segment should be in the SYSTEM.

State what transpires when the constraint name is not included in the constraint clause.

This will lead into a situation whereby the oracle system uses a default name like SYS_Cxxxx where xxxx is a number generated by the system. This habit is not advisable as it disguises the process of tracking the tables to which particular constraints belong or even the work of a given constraint.

Describe clearly what would transpire in an event that a tablespace clause is omitted from the primary key constraint clause.

The result of the above situation is that one will get an index which is generated automatically being placed in the default tablespace. Definitely, this occurrence has high probability of causing system performance problems since this will usually be the same tablespace for the table which is being created thus a collision.

Owing to the fact that performance of any system is one of the factors that users consider when procuring computer systems, one is highly encouraged to abstain from this habit as it contradicts with the goals of system development.

State what happens in event that one disables the primary key constraints and later on enables it without specifying the primary index fully.

In such a situation, the following becomes the outcome:

• All sizing information is lost.
• There is creation of the index in the default tablespace which belongs to the user.

It is worth noting that this information is not stored by the oracle as part of the definition of the constraint yet are stored as part of the index definition such that when the constraint is disabled, the index is dropped leading to loss of information.

Explain the cause of the error called ‘snapshot too old’ stating its prevention or even ways with which it can be mitigated.

This type of error arises in situations where there is large or transactions running for long having been either wrapped into their pertinent rollback space or have had their rollback space being written on by other transactions. The easiest way to mitigate or prevent this from happening is to employ the divide and rule method! Here one breaks the transactions into smaller units or increases the sizes of segments for rollback as well as there extents

How to identify invalid database object?

Check the status column of the DBE_, ALL or USER_OBJECTS as determined by the kind of permission at your disposal, i.e. whether you have permission on the view or you are using a DBA account.

State the best way of obtaining sizing data to be used in production table implementation assuming one has an example table.

Here, one analyses the table as well as the user data that has been provided in the DBA_TABLES view in order to get pertinent data for calculation including the average row length. There another way – to count the number of blocks in the table actually and ratio the number of blocks in the table to the number of blocks vis-à-vis number of expected rows, though this procedure is considered quick but dirty.

How to find out the number of users logged in a database at any particular time with their operating system id?

Many ways lead to this problem’s solution e.g. looking at the v$sesion or the v$process

One can also consider checking the current_login parameters found at the v$sysstat view.

For UNIX users, one can try to do "ps -ef|grep oracle|wc -l? command though this works only against a single instance of installation.

Describe the recommended way to disable and re-enable a primary key constraint.

Here one uses the ALTER TABLE command in both occasions only that when it comes to enable clause, one is expected to specify the USING INDEX and the TABLESPACE clause that are pertained to the primary key. Identify the time when it is necessary that one uses more than one database writer process stating the number that should be used at such times