Interview questions about Oracle

Oracle

Oracle database is a relational database management system. It is known as Oracle database, OracleDB or simply Oracle. It is produced and marketed by Oracle Corporation. Oracle is a secured database that is widely used in multinational companies. The frequently asked questions from oracle database are given below.

Oracle database is the first database designed for enterprise grid computing. The enterprise grid computing provides the most flexible and cost effective way to manage information and applications.

Best Interview Questions about Oracle

1. What is a table space?

A database contains Logical Storage Unit called table spaces. A table space is a set of related logical structures. Actually a table space groups related logical structures together.

2. How will you differentiate between VARCHAR & VARCHAR2?

Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length. Their differences are:
•VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.
• VARCHAR will hold the space for characters defined during declaration even if all of them are not used whereas VARCHAR2 will release the unused space.

3. What is a SYSTEM table space and when it is created?

When the database is created in Oracle database system, it automatically generate a SYSTEM named SYSTEM table space. The SYSTEM tablespace contains data dictionary tables for the entire database.

4. What the difference between TRUNCATE & DELETE command?

Both the commands are used to remove data from the database.
The difference between the two includes:

• Truncate is a DDL operation while delete is a DML operation.
• Truncate drops the structure of a database and hence cannot be rolled back while the delete command can be rolled back.
• The Truncate command will free the object storage space while the delete command does not.

5. What is an Oracle table?

A table is basic unit of data storage in Oracle database. A table contains all the accessible information of a user in rows and columns.

6. What are nested tables?

Nested table is a data type in Oracle which is used to support columns containing multi valued attributes. It also hold entire sub table

7. How TRANSLATE command is different from REPLACE?

TRANSLATE command translates characters one by one in the provided string with the substitution character. REPLACE command will replace a character or a set of characters with a complete substitution string.

For Example:
TRANSLATE (‘Mississippi’,’is’,’15) => M155151pp1
REPLACE (‘Mississippi’,’is’,’15) => M15s15ippi

8. In the Oracle version 9.3.0.5.0, what does each number shows?

Oracle version number refers:
o 9 – Major database release number
o 3 – Database maintenance release number
o 0 – Application server release number
o 5 – Component Specific release number
o 0 – Platform Specific release number

9. Why do we use COALESCE function in Oracle?

COALESCE function is used to return the first non-null expression from the list of arguments provided in the expression. There must be a minimum of two arguments in an expression.
Syntax:
Coalesce ( expr 1, expr 2, expr 3…. Expr n

10. What is WITH CHECK OPTION?

The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.

11. What is bulk copy or BCP in Oracle?

Bulk copy or BCP in Oracle, is used to import or export data from tables and views but it does not copy structure of same data.
The main advantage of BCP is fast mechanism for coping data and you can also take the backup of data easily.

12. What is a snapshot in Oracle database?

A snapshot is a replica of a target master table from a single point-in-time. In simple words you can say, snapshot is a copy of a table on a remote database.

13. What is the quickest way to fetch the data from a table?

The quickest way to fetch the data would be to use ROWID in the SQL query.

14. How many memory layers are in the Oracle shared pool?

Oracle shared pools contains two layers:
1. library cache
2. data dictionary cache

15. Why do we need integrity constraints in a database?

Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables.
Various integrity constraints are available which include Primary Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.

16. What is savepoint in Oracle database?

Save points are used to divide a transaction into smaller parts. It allows rolling back of a transaction. Maximum five save points are allowed. It is used to save our data, whenever you encounter an error you can roll back from the point where you save your SAVEPOINT.

17. What is hash cluster in Oracle?

Hash cluster is a technique to store a data in hash table and improve the performance of data retrieval. Hash function is applied on table row’s cluster key value and store in hash cluster.

18. What is the use of Aggregate functions in Oracle?

Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations. These are:
• AVG
• MIN
• MAX
• COUNT
• SUM
• STDEV

19. What are the different types of modules in Oracle forms?

Following are the different modules in Oracle forms:
o Form module
o Menu module
o Pl/SQL Library module
o Object Library module

20. What is RAW data type?

RAW data type is used to store values in binary data format the maximum size for a raw in a table in 32767 bytes.

21. Can you create a synonym without having a table?

Yes. We can create a synonym without having a base table.

22. What types of joins are used in writing SUBQUERIES?

o Self join
o Outer Join
o Equi-join

23. What is the use of NVL function?

o The NVL function is used to replace NULL values with another or given value. Example is –
o NVL(Value, replace value)

24. What is the usage of control file in Oracle?

In Oracle, control file is used for database recovery. The control file is also used to identify the database and redo log files that must be opened for database operation to go ahead, whenever an instance of an ORACLE database begins.

25. What is the usage of Merge Statement?

Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations.