Oracle Interview Questions and Answers

1. Explain: 

a.) Integrity Constraints

It can be called as a declarative way in order to define a business rule for a table's column

b.) Index

- It can be called as an optional structure which is associated with a table for direct access to the rows
- Index can be created for one or more columns in a table

c.) Extent

- It can be defined as a specific number of contiguous data blocks in single allocation.
- It is used to store a specific type of information.

2. What is ANALYZE command used for?

ANALYZE command is used to perform various functions on index, table, or cluster, as listed below:
- It helps in dentifying migrated and chained rows of the table or cluster.
- It helps in validating the structure of the object.
- It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.
- It helps in deleting statistics used by object from the data dictionary. 

3.
 a.) List the types of joins used in writing SUBQUERIES.

- Self join
- Outer Join
- Equi-join

b.) List the various Oracle database objects.

- TABLES
- VIEWS
- INDEXES
- SYNONYMS
- SEQUENCES
- TABLESPACES

4.
a.) Explain
 i.)Rename ii.)Alias.

- Rename - It is a permanent name provided to a table or column.

- Alias - It is a temporary name provided to a table or column which gets over after the execution of SQL statement.

b.) What is a view?

- It is virtual table which is defined as a stored procedure based on one or more tables.

5.
a.) What are the various components of physical database structure of Oracle database?

Oracle database comprises of three kinds of files:
- Data-files,
- Redo log files,
- Control files.

b.) List out the components of logical database structure of Oracle database.

- Table-spaces
- Database's schema objects.

6. a.) What do you mean by a table-space?

- These are the Logical Storage Units into which a database is divided.
- It is used to group together the related logical structures.

b.) What is Control File used for?

Control File is used for:
- Database recovery.
- Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database operation to go ahead.

7. a.) What is a synonym? What are its various types?

A synonym can be called as an alias for a table, view, sequence or program unit. It is basically of two types:
- Private - Only the owner can access it.
- Public - Can be accessed by any database user.

b.) What are the uses of synonyms?

- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.

8.) What do you mean by a deadlock?

- When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock.
- The reasons for it to happen are:
- lack of proper row lock commands.
- Poor design of front-end application
- It reduces the performance of the server severely.
- These locks get automatically released automatically when a commit/rollback operation is performed or any process is killed externally.

9.)
a.) What suggestions do you have to reduce the network traffic?

Following are some of the actions which can be taken to reduce the network traffic:

- Use snapshots to replicate data.
- Use remote procedure calls.
- Replicate data in distributed environment.

b.) What are the various types of snapshots ?

There are two types of snapshots:

- Simple snapshots - Based on a query that does not contain GROUP BY clauses, CONNECT BY clauses,

JOINs, sub-query or snashot of operations.

- Complex snapshots- which contains atleast any one of the above.

10.) What is a sub query? What are its various types?

- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
- A sub query is added in the where clause of the main query.

There can be two types of subqueries:

a.) Correlated sub query
- It can reference column in a table listed in the from list of the outer query but is not as independent as a query. .

b.) Non-Correlated sub query
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query

11.) Will you be able to store pictures in the database?Explain.

- Yes, pictures can be stored in the database using Long Raw Data type.
- This datatype is used to store binary data for 2 gigabytes of length.
- However, the table can have only one Long Raw data type.

12.) Explain: a.) Integrity constraint.

- It is a declaration defined in a business rule for a table column.
- Integrity constraint ensures the accuracy and consistency of data in a database.
- It is of three types - namely - Domain Integrity, Referential Integrity and Domain Integrity.

b.) COALESCE function

- This function is used to return the value which is set not to be null in the list.
- Incase all values in the list are null the coalesce function will return NULL.
- Its representation:
Coalesce(value1, value2, value3,…)

13.) Explain the following: a.) BLOB datatype.

- It is a data type with varying length binary string, used to store two gigabytes memory.
- For BLOB, the length needs to be specified in bytes.

b.) DML.

- DML - it is also termed as Data Manipulation Language (DML).
- It is used to access and manipulate data in the existing objects.
- DML statements are insert, select, update and delete.

14.) Differentiate between: a.) TRANSLATE and REPLACE.

- Translate is used to substitute character by character.
- Replace is used to substitute a single character with a word.

b.) What is Merge Statement used for?

- Merge statement is used to select rows from one or more data source to updating and insert into a table or a view.

15.) What are the various Oracle Database objects?

Various database objects are as follows:

- Tables – This is a set of elements organized in vertical and horizontal fashion.
- Tablespaces – This is a logical storage unit in Oracle.
- Views – It is virtual table derived from one or more tables.
- Indexes – This is a performance tuning method to process the records.
- Synonyms – This is a name for tables.
- Sequences.

16. What is the purpose of Save Points in Oracle database?

- Save Points are used to divide a transaction into smaller phases.
- It enables rolling back part of a transaction.
- Maximum 5 save points are allowed in Oracle Database.
- Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.

17. a.) What is an ALERT?

- It a window which appears on the screen overlaying a portion of the current display.

b.) Differentiate between post-database commit and post-form commit.

- The post-database commit trigger is fired after oracle forms issue the commit to finalized transactions.
- The post-form commit is fired during the post and commit transactions process, after the database commit occurs.

18. Differentiate between pre-select and pre-query.

- Once oracle forms construct the select statement to be issued Pre-select is fired during the execute query and count query processing. All this happens before the statement is actually issued.
- The pre-query trigger is fired just before oracle forms issue the select statement to the database.

19. What is hot backup and logical backup?

Hot backup

- Backing up the archive log files when database is open is called Hot backup.
- To do this, the ARCHIVELOG mode is enabled.
- Following files are backed up - All data files, Archive log, redo log files and control files.

Logical backup

- Logical back ip is reading a set of database records and writing them into a file.
- An Export utility is required to take the backup while an Import utility is required to recover from the backup.

20. What do you mean by Redo Log file mirroring ?

- The process of having a copy of redo log files is called mirroring.
- It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group.
- In case a group fails, the database automatically switches over to the next group. It diminishes the performance. 

What is trigger in oracle? 

Triggers are constructs in PL/SQL that need to be just created and associated with a table. Once they are created, when the table associated with it gets updated due to an UPDATE, INSERT or a DELETE, the triggers get implicitly fired depending upon the instructions passed to them

What are the types of triggers? 

The types of triggers are:

• Row level triggers
• Statement level triggers
• BEFORE and AFTER triggers

What are the types of triggers and how the sequence of firing in text item. 

The different types of triggers are:

• Row triggers and statement triggers: specifies how many times the trigger should get executed – Once for every row or once for every triggering statement.
• Before and after triggers – Before trigger is fired before and update, insert or triggering statement is executed. After trigger is fired after the triggering statement is executed.
• Instead of Trigger- Here, the trigger is fired instead of executing the triggering statement
• Key triggers – fired on some key action.
• Mouse trigger- fired on some mouse navigation
• Navigational trigger – fired as a result of some navigation.

The sequence of firing in a text item are as follows::
a) pre - text
b) when new item
c) key-next
d) when validate
e) post text.

What are triggering attributes? 

Triggers can be fired based on the following criteria:
Category - (INSERT, DELETE, UPDATE) i.e. which kind of DML statement causes the trigger to fire.
Timing – (BEFORE or AFTER) i.e. whether the trigger fires before the statement is executed of after.
Level – (Row or Statement) i.e. whether it fires once for each row affected by trigger statement or whether it fires once.

Difference between database triggers and form triggers. 

Database triggers (DBA)

• Fired when a DML operation is performed
• They manipulate data stored in Oracle tables
• They can cause other database triggers to fire.
• They can be fired from any session executing the triggering DML statements.

Form trigger (FT)

• Fired in response to any event that takes place while working with the forms.
• They manipulate data in Oracle tables as well as variables in forms.
• They cannot cause other form triggers to fire, but can fire other database triggers.
• They can be fired only from the form that define the trigger

- Explain the difference between trigger and stored procedure.

• A stored procedure can accept parameters while a trigger cannot.
• A trigger can’t return any value while stored procedures can.
• A trigger is executed automatically on some event while a stored procedure needs to be explicitly called.
• Triggers are used for insertions, update and deletions on tables while stored procedures are often using independently in the database.
• A trigger cannot be written in a stored procedure. However, the reverse is not possible.
• Trigger in act which is performed automatically before or after a event occur
  Stored procedure is a set of functionality which is executed when it is explicitly invoked.  
• Procedure runs only when one call them manually whereas a trigger runs when there is any activity (insert,update,delete) on table on which the trigger is written.
• Triggers can execute every time some field in database is updated. If a field is likely to be updated often.

Row level and statement level trigger

Explain Row level and statement level trigger.

A trigger if specified FOR EACH ROW; it is fired for each of the table being affected by the triggering statement. For example if a trigger needs to be fired when rows of a table are deleted, it will be fired as many times the rows are deleted.
If FOR EACH ROW is not specified, it is application to a statement and the trigger is executed at a statement level.

Row-level: - They get fired once for each row in a table affected by the statements.
Statement: - They get fired once for each triggering statement.

Row Level TriggerRow Level Trigger is fired each time row is affected by Insert, Update or Delete command. If statement doesn’t affect any row, no trigger action happens.

Statement Level Trigger
This kind of trigger fires when a SQL statement affects the rows of the table. The trigger activates and performs its activity irrespective of number of rows affected due to SQL statement.

Disadvantages of trigger in oracle.

• Triggers can execute every time some field in database is updated. If a field is likely to be updated often, it is a system overhead.
• Viewing a trigger is difficult compared to tables, views stored procedures.
• It is not possible to track or debug triggers.