Helpful Interview Questions for Oracle Jobs

What are the difference between and constraints and triggers?

Constraints are used to maintain the integrity and atomicity of database .in other words it can be said they are used to prevent invalid data entry. The main 5 constraints are NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE KEY and CHECK.
Triggers are basically stored procedures, which automatically fired when any insert, update or delete is issued on table.

Another most imp. Diff. is that trigger effected only those row after which trigger applied but constraint effected all row of table.

What is normalization? What is the advantage of normalization (briefly)?

1. The process of separating data into distinct, unique sets is called normalization. This is implemented to improve the performance of the RDBMS, such as reduces redundancy of data and data inconsistency. 

2. Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability.

3. Database normalization is a series of steps followed to obtain a database design that allows for consistent storage and efficient access of data in a relational database. These steps reduce data redundancy and the risk of data becoming inconsistent.

4. Normalization is the process used to reduce the unnecessary repetition of data i.e., redundant data. It is performed on the data, which is redundant and makes the data in a normalized format. It is of step-by-step process IstNotmal, FormIIndNormal, formIIIrdNormal, formIVthNormal form or Boyce odd Normal form By performing this we will get the data in the Normalized formati.,e from DBMS to RDBMS.

How the logs are escalated?

There are different types are logs which are logged by Oracle Database Engine. A DBA has to look in for alter log which exist background dump dust. The seviourity of the problem are analysed and escalated based on the information provided by the alter log.

What is structure of Database?

1. Oracle database usually contains one database and a single instance. But, Oracle 9i, 10g RAC (Real Application Clusters) can have multiple instances to interact with a single database for high availability.
Instance is non-persistent, memory based background processes and structures.
Database is persistent, disk based, data and control files

2. Physical Structure of Database:
One or more data files, control file(s), Redo log file(s) and init.ora file.
Logical Structure of Database:
Table spaces, segments, extents, blocks

What is the difference between primary key, unique key, and surrogate key?

Primary Key: A column in a table whose values uniquely identify the rows in the table. A primary key value cannot be NULL. 
Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one row for each unique key value. 
Surrogate Key: A system generated key with no business value. Usually implemented with database-generated sequences.

Primary Key                        Unique key
1.There is only one        1.  There may be more than 1
    Primary key for I table                        Unique Key in table 

2.It can contain Null Value                    2.  It can contain Null Value
1. what are the diffrent file types that are supported by SQL*Loader? method (skips dbcache, no redo generation)

b. conventional method (just opposite of direct load)

How to find how many database reside in Oracle server in query?

select count(*) from v$database;

What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly? 

CBO (Cost Based Optimizer): Generates an execution plan for a SQL statement Optimizer_index_cost_adj parameter can be set to help CBO to decide an execution plan, which affects the speed of SQL query. We can also make necessary changes to the following parameters to effect CBO performance:
Optimizer_search_limit & optimizer_max_permutations

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.