Going for oracle job interview? If yes, here’s the most asked top 20 oracle interview questions that will help you great in the interview.
Top 20 Oracle Interview Questions
1. In which language Oracle has been developed?
Oracle has been developed using C Language.
2. What is the difference between UNIQUE or PRIMARY KEY Constraint?
A column defined as PRIMARY KEY can contain unique values same as UNIQUE but UNIQUE can contain NULLs while a column defined as PRIMARY KEY cannot contain NULLs.
3. Explain Integrity Constraints, Index, Extent.
It can be called as a declarative way in order to define a business rule for a table’s column
– 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
– 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.
4. What is the use of NVL function?
The NVL function is used to replace NULL values with another or given value.
5. What is difference between %ROWTYPE and Type Record?
%ROWTYPE is its accept multiple data type.to be used whenever query returns an entire row of a table or view.
TYPE RECORD is accept single data type.to be used whenever query returns columns of different table or views and variables.
6. What is BLOB datatype?
A BLOB data type is a varying length binary string which is used to store two gigabytes memory. Length should be specified in Bytes for BLOB.
7. List the types of joins used in writing SUBQUERIES.
– Self join
– Outer Join
8. What is PL/SQL? Also structure of PL/SQL?
PL/SQL is a programming language. SQL and procedural programming language constructs such as named blocks,iterations, conditional branching.
PL/SQL uses block structure as its basic two structure. Anonymous blocks or nested blocks can be used in PL/SQL.
9. What is difference between PROCEDURE and FUNCTION?
A PROCEDURE may return one or more values or may not return at all. A FUNCTION must returns a value using the return statement.
10. What is NULL value in oracle?
NULL value represents missing or unknown data. This is used as a place holder or represented it in as default entry to indicate that there is no actual data present.
11. What is difference between TRUNCATE and DELETE?
Truncate is DML Statements. Cannot be rolled back. Delete allows the filtered deletion. Deleted records can be rolled back or committed.
12. 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.
There are few reasons due to which deadlock happens 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.
13. What are temporal data types in Oracle?
Oracle provides following temporal data types:
- Date Data Type – Different formats of Dates
- TimeStamp Data Type – Different formats of Time Stamp
- Interval Data Type – Interval between dates and time
14. How do we create privileges in Oracle?
A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.
GRANT user1 TO user2 WITH MANAGER OPTION;
15. What is the difference between rename and alias?
Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but replacement of name and Alias is an alternate name of the table or column.
16. What is the difference between Trigger and Stored Procedure?
Trigger in act which is performed automatically before or after a event occur when DML operations are occur, Trigger is Fire.
Stored procedure is a set of functionality which is executed when it is explicitly invoked.
17. What are the various types of snapshots?
There are two types of snapshots:
- Simple Snapshots
- Complex 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.
18. What are the various constraints used in Oracle?
Following are the constraints used:
- NULL – It is to indicate that particular column can contain NULL values.
- NOT NULL – It is to indicate that particular column cannot contain NULL values.
- CHECK – Validate that values in the given column to meet the specific criteria.
- DEFAULT – It is to indicate the value is assigned to default value.
19. What is the difference between $ORACLE_BASE and $ORACLE_HOME?
Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.
20. What is the difference 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.
These are the most asked top 20 oracle interview questions. Hope these will be very helpful in the interview. Good luck..:)