Oracle Interview Questions Page 1

Page 1Page 2Page 3Page 4Page 5Page 6Page 7Page 8

1.Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process.

In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2.You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

3.How do you switch from an init.ora file to a spfile?

Issue the create spfile from pfile command

4.Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object

5.Give two examples of how you might determine the structure of the table DEPT.

Use the describe command or use the dbms_metadata.get_ddl package.

6.Where would you look for errors from the database engine?
In the alert log.

7.Compare and contrast TRUNCATE and DELETE for a table.

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

8.Give the reasoning behind using an index.

Faster access to data blocks in a table.

9.Give the two types of tables involved in producing a star schema and the type of data they hold.

Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

0.What type of index should you use on a fact table?

A Bitmap index.

11.Give two examples of referential integrity constraints.

A primary key and a foreign key.

12.A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

13.Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any ball in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any ball in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

14.What command would you use to create a backup control file?

Alter database backup control file to trace.

15.Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT - Instance startup

STARTUP MOUNT - The database is mounted

STARTUP OPEN - The database is opened

16.What column differentiates the V$ views to the GV$ views and how?

The INST_ID column which indicates the instance in a RAC environment the information came from.

17.How would you go about generating an EXPLAIN plan?

Create a plan table with utlxplan.sql.

Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement

Look at the explain plan with utlxplp.sql or utlxpls.sql

18.How would you go about increasing the buffer cache hit ratio?

Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

19.Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

20.Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

1.Describe the difference between a procedure, function and anonymous pl/sql block.

Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn?t have to.

2.What is a mutating table error and how can you get around it?

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

3.Describe the use of %ROWTYPE and %TYPE in PL/SQL

Expected answer: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

4.4What packages (if any) has Oracle provided for use by developers?

Expected answer: Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

5.Describe the use of PL/SQL tables

Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

6.When is a declare statement needed ?

The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

7.In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?

Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

8.What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

9.How can you find within a PL/SQL block, if a cursor is open?

Expected answer: Use the %ISOPEN cursor status variable.

10.How can you generate debugging output from PL/SQL?

Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.

11.What are the types of triggers?

Expected Answer: There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:

BEFORE ALL ROW INSERT

AFTER ALL ROW INSERT

BEFORE INSERT

AFTER INSERT etc.


1.In a system with an average of 40 concurrent users you get the following from a query on rollback extents:

ROLLBACK CUR EXTENTS

--------------------- --------------------------

R01 11

R02 8

R03 12

R04 9

SYSTEM 4

2.You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action

No there is not a problem. You have 40 extents showing and an average of 40 concurrent users. Since there is plenty of room to grow no action is needed.

3.You see multiple extents in the temporary tablespace. Is this a problem

As long as they are all the same size this isn?t a problem. In fact, it can even improve performance since Oracle won?t have to create a new extent when a user needs one.

4.Define OFA.

OFA stands for Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system so that you get the maximum flexibility for future tuning and file placement.

5.How do you set up your tablespace on installation

The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation os SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on another, ROLLBACK tablespace on another and still have two for DATA and INDEXES. They should indicate how they will handle archive logs and exports as well. As long as they have a logical plan for combining or further separation more or less disks can be specified.

6.What should be done prior to installing Oracle (for the OS and the disks)

adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.

7.You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem

Check to make sure that the archiver isn?t stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.

8.When configuring SQLNET on the server what files must be set up

INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file

9.When configuring SQLNET on the client what files need to be set up

SQLNET.ORA, TNSNAMES.ORA

10.What must be installed with ODBC on the client in order for it to work with Oracle

SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.