Database Interview Questions Part 1

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

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

3.What is WITH CHECK OPTION?

The WITH CHECK option clause specifies the level of check to be done in DML statements. It aids in preventing changes to a view that would produce results not contained in the sub query.

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

5.What is the use of NVL function?

The NVL function is used for replacing NULL values with given or another value. E.g.  NVL(Value, replace value)

6.How do we get field detail of a table?

To get the field of a specified table use, Describe <Table_Name>

7.What is an ALERT?

An alert is a window which appears in the center of the screen and overlays a portion of the current play.

8.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 ROW ID is the fastest query method for fetching data from the table.

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

10.What is hash cluster?

Hash Cluster is a technique used for storing the table to make it faster to retrieve. It order to retrieve the rows from the table, apply the hash value on the table.

11.What are SET operators?

SET operators are used with two or more queries. The operators are Union, Union All, intersect and Minus.

12.What is a view?

View is a logical table that is based on one or more tables and views. The tables which the view is based upon are called Base Tables and it contains no data.

13.What are the different types of storage systems available and which one is used by Oracle?

Two types of storage systems are available

Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)

– Most databases use RDBMS model, Oracle also uses RDBMS model.

– Hierarchical Storage Management System (HSM)

– Information Management System (IMS) from IBM.

– Integrated Database Management System (IDMS) from CA.

14.Difference between Logical and Physical data model.

– Logical data model represents database in terms of logical objects, such as entities and relationships.

– Physical data model represents database in terms of physical objects, such as tables and constraints.

15.Differentiate between a database and Instance and explain relation between them?

– Database is a collection of three important files which include data files, control files and redo log files which physically exist on a disk

– Whereas instance is a combination of oracle background process (SMON, PMON, DBWR, LGWR) and memory structure (SGA, PGA).

– Oracle background processes running on a computer share same memory area.

– An instance can mount and open only a single database, ever.

– A database may be mounted and opened by one or more instances (using RAC).

16.What are the components of SGA?

– SGA is used to store shared information across all database users.

– It mainly includes Library cache, Data Dictionary cache, Database Buffer Cache, Redo log Buffer cache, Shared Pool.

Library cache – It is used to store Oracle statements.

Data Dictionary Cache – It contains the definition of Database objects and privileges granted to users.

Data Base buffer cache – It holds copies of data blocks which are frequently accessed, so that they can be retrieved faster for any future requests.

Redo log buffer cache – It records all changes made to the data files.

17.What are the disk components in Oracle?

These are the physical components which gets stored in the disk.

– Data files

– Redo Log files

– Control files

– Password files

– Parameter files

18.What is System Change Number (SCN)?

– SCN is a unique ID that Oracle generates for every committed transaction.

– It is recorded for every change in the redo entry.

– SCN is also generated for every checkpoint (CKPT) occurred.

– It is an ever increasing number which is updated for every 3 seconds

– You can get the SCN number by querying select SCN from v$database from SQLPLUS.

19.What is Database Writer (DBWR) and when does DBWR write to the data file?

– DBWR is a background process that writes data blocks information from Database buffer cache to data files.

There are 4 important situations when DBWR writes to data file

– Every 3 seconds

– Whenever checkpoint occurs

– When server process needs free space in database buffer cache to read new blocks.

– Whenever number of changed blocks reaches a maximum value.

20.What is Log Writer and when does LGWR writes to log file?

– LGWR writes redo or changed information from redo log buffer cache to redo log files in database.

– It is responsible for moving redo buffer information to online redo log files, when you commit and a log switch also occurs.

– LGWR writes to redo files when the redo log buffer is 1/3 rd full.

– It also writes for every 3 seconds.

– Before DBWR writes modified blocks to the datafiles, LGWR writes to the log file

21.What is the Difference between SGA and PGA?

– SGA (System Global Area) is a memory area allocated during an instance start up.

– SGA is allocated as 40% of RAM size by default.

– SGA size is controlled by DB_CACHE_SIZE parameter defined in initialization parameter file (init.ora file or SPFILE).

– PGA (Program or Process Global Area) is a memory area that stores a user session specific information.

– PGA is allocated as 10% of RAM size by default.