Oracle Interview Questions
 
1.You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for The first thing to check with a large SGA is that it isn?t being swapped out.

2.What OS user should be used for the first part of an Oracle installation (on UNIX)
You must use root first.

3.When should the default values for Oracle initialization parameters be used as is
Never

4.How many control files should you have? Where should they be located
At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.

5.How many redo logs should you have and how should they be configured for maximum recoverability

You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.

6.You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces

At least 7, see disk configuration answer above.

7.Describe third normal form

Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key

8.Is the following statement true or false:

"All relational databases must be in third normal form"

False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.

9.What is an ERD

An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.

10.Why are recursive relationships bad? How do you resolve them

A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn?t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.

11.What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")

Expected answer: This means the two entities should probably be made into one entity.

12.How should a many-to-many relationship be handled

By adding an intersection entity table

13.What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used

A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.