Interview questions for Oracle Database

1. What is Redo Log Files?

The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

2. What is a tablespace?

A database contains Logical Storage Unit called tablespaces. A tablespace is a set of related logical structures. Actually a tablespace groups related logical structures together.

3. What is Archive Log Files?

Oracle automatically archives log files when the database is in ARCHIVELOG mode. This prevents oracle from overwriting the redo log files before they have been safely archived to another location.

4. What is a SYSTEM tablespace and when it is created?

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

5. What is Parameter Files (initSID.ora)?

Parameter files contain a list of configuration parameters for that instance and database.

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

Oracle version number refers:

• 9 – Major database release number
• 3 – Database maintenance release number
• 0 – Application server release number
• 5 – Component Specific release number
• 0 – Platform Specific release number

7. What is data blocks?

Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk. The data blocks can be 4 K,8 K size depending on the requirement.

8. What is the relationship among database, tablespace and data file?

An Oracle database contains one or more logical storage units called tablespaces. These tablespaces collectively store whole data of databases and each tablespace in Oracle database consists of one or more files called datafiles. These datafiles are physical structure that confirm with the operating system in which Oracle is running.

9. What is Extent?

Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.

10. What are the components of physical database structure of Oracle database?

Components of physical database structure are given below.

• One or more data files.
• Two or more redo log files.
• One or more control files.

11. What is a Segment ?

A segment is a collection of extents which is used for storing a specific data structure and resides in the same tablespace.

12. 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.

13. What is RAW datatype?

RAW data type is used in storing values in binary data format. The maximum size of a RAW in a table is 32767 bytes.

14. What is Rollback Segment ?

Database contain one or more Rollback Segments to roll back transactions and data recovery.

15. What is BLOB datatype?

A BLOB data type is a binary string with a varying length which is used in storing two gigabytes memory. Length should be stated in Bytes for BLOB

16. What is archivelog and Noarchive log mode?

We all know that redo logs stored the redo information and redo log files are in circular fashion.Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving.

17. What is NULL value in Oracle?

NULL value represents unknown or missing data. It is used as a place holder or represented as a default entry indicating that no actual data is present.

18. What are the various Oracle database objects?

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.

19. What is the difference between varchar and varchar2 data types?

Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes. While Varchar will occupy space for NULL value, Varchar2 will not occupy any space. They are differentiated by space.

20. What is the fastest query method to fetch data from the table?

You can use ROWID to fetch Row from the table. The use of ROWID is the fastest query method for fetching data from the table.

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

Following are the different modules in Oracle forms:

• Form module
• Menu module
• Pl/SQL Library module
• Object Library module

22. What is the parameter mode that can be passed to a procedure?

The parameter modes that can be passed to a procedure are IN, OUT and INOUT

23. 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.

24. What is that trace files contains and the utility used to read them?

Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tipoff utility is used to convert trace file into readable format.

25. What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. 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. If the group fails, the database automatically switches over to the next group. It diminishes the performance.