Oracle Interview Questions – Part 32

Q.Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Q.Explain the use of table functions?

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

Q.What background process refreshes materialized views?

The Job Queue Processes.

Q.How would you determine what sessions are connected and what resources they are waiting for?


Q.How would you determine who has added a row to a table?

Turn on fine grain auditing for the table.

Q.How can you rebuild an index?


Q.What is an Oracle Instance?
An Oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The combination of the background processes and memory buffers is called an Oracle instance. We can run multiple instances on the same Oracle Database Server, where each instance connects to its database.
Oracle instance includes:
SGA – System or Shared Global Area
Components of SGA:
•DBBC – Database Buffer Cache
•SP – Shared Pool; divided into Library Cache (LC) and Data Dictionary Cache (DDC) or Row Cache.
•RLB – Redo log Buffer
Background Process (10/11g database):
Mandatory Processes
•SMON – System Monitor
•PMON – Process Monitor
•DBWR – Database writer
•LGWR – Log Writer
•CKPT – Check point
•RECO – Recoverer
•DIAG – Diagnosability (new in 11g)
•VKTM – Virtual keeper of time (keeps “SGA Time” variable in current, new in 11g)
Optional Process
•ARCN – Archiver
•MMAN – Memory Manager – ASMM
•MMON – Memory Monitor
•MMNL – Memory Monitor Light – AWR
and few more…

TIP: For a complete overview of Database 11g Architecture
check out this poster: Database 11g Architecture Poster [2.74 MB]

List of running processes of a single instance (11g) on Linux:
[[email protected] ~]$ top -n 1 -U oracle -c
9181 oracle 15 0 745m 111m 109m S 6.9 7.5 1:11.15 ora_j000_DB1_SID
9163 oracle 16 0 745m 68m 65m S 5.3 4.6 0:11.95 ora_j001_DB1_SID
10420 oracle 18 0 744m 12m 11m R 3.0 0.8 0:00.09 ora_j002_DB1_SID
6773 oracle 16 0 748m 40m 34m S 0.7 2.7 0:03.16 ora_dbw0_DB1_SID
6775 oracle 16 0 759m 34m 33m S 0.7 2.4 0:10.74 ora_lgwr_DB1_SID
6767 oracle 15 0 744m 13m 11m S 0.3 0.9 0:02.17 ora_psp0_DB1_SID
6785 oracle 15 0 744m 19m 18m S 0.3 1.3 0:02.66 ora_mmnl_DB1_SID
6560 oracle 18 0 42048 9348 6788 S 0.0 0.6 0:00.86 tnslsnr LISTENER -inherit
6755 oracle 15 0 744m 16m 14m S 0.0 1.1 0:02.50 ora_pmon_DB1_SID
6757 oracle -2 0 744m 13m 11m S 0.0 0.9 0:04.31 ora_vktm_DB1_SID
6761 oracle 15 0 744m 13m 11m S 0.0 0.9 0:00.34 ora_gen0_DB1_SID
6763 oracle 18 0 744m 12m 11m S 0.0 0.9 0:00.53 ora_diag_DB1_SID
6765 oracle 15 0 744m 19m 18m S 0.0 1.3 0:00.59 ora_dbrm_DB1_SID
6769 oracle 18 0 744m 16m 14m S 0.0 1.1 0:07.11 ora_dia0_DB1_SID
6771 oracle 18 0 744m 17m 16m S 0.0 1.2 0:11.13 ora_mman_DB1_SID
6777 oracle 16 0 744m 16m 14m S 0.0 1.1 0:08.51 ora_ckpt_DB1_SID
6779 oracle 15 0 748m 87m 84m S 0.0 5.9 0:04.61 ora_smon_DB1_SID
6781 oracle 18 0 744m 18m 17m R 0.0 1.3 0:00.52 ora_reco_DB1_SID
6783 oracle 15 0 748m 56m 51m S 0.0 3.8 0:06.01 ora_mmon_DB1_SID
6787 oracle 15 0 744m 13m 11m S 0.0 0.9 0:00.35 ora_d000_DB1_SID
6789 oracle 15 0 744m 12m 11m S 0.0 0.8 0:00.31 ora_s000_DB1_SID
6852 oracle 18 0 744m 14m 13m S 0.0 1.0 0:00.40 ora_qmnc_DB1_SID
6859 oracle 15 0 744m 25m 23m S 0.0 1.7 0:00.53 ora_q000_DB1_SID
6864 oracle 18 0 744m 15m 14m S 0.0 1.0 0:00.21 ora_q001_DB1_SID
6983 oracle 15 0 748m 54m 48m S 0.0 3.7 0:05.40 ora_cjq0_DB1_SID
7141 oracle 15 0 744m 13m 12m S 0.0 0.9 0:00.26 ora_smco_DB1_SID
7722 oracle 16 0 753m 58m 55m S 0.0 4.0 0:07.64 oracleDB1_SID (LOCAL=NO)
10254 oracle 15 0 744m 14m 12m S 0.0 1.0 0:00.10 ora_w000_DB1_SID

Q.What information is stored in Control File?

Oracle Database must have at least one control file.
It’s a binary file contains some of the following information:
•The database name and unique ID
•The timestamp of database creation
•The names and locations of associated datafiles and redo log files
•Tablespace information
•Datafile offline ranges
•Archived log information and history
•Backup set and backup piece information
•Backup datafile and redo log information
•Datafile copy information
•Log records: sequence numbers, SCN range in each log
•RMAN Catalog
•Database block corruption information

The location of the control files is specified through the control_files init param:
[email protected]_SID SQL>show parameter control_file;
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/DB1_SID
/control01.ctl, /u01/app/oracl

Q.How do you control number of Datafiles one can have in an Oracle database?

The db_files parameter is a “soft limit ” parameter that controls the maximum number of physical OS files that can map to an Oracle instance.
The maxdatafiles parameter is a different – “hard limit” parameter.
When issuing a “create database” command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32.
The maximum number of database files can be set with the init parameter db_files.

Q.How many Maximum Datafiles can there be in Oracle Database?

Regardless of the setting of this paramter, maximum per database: 65533 (May be less on some operating systems)
Maximum number of datafiles per tablespace: OS dependent = usually 1022
Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Bigfile tablespaces can contain only one file, but that file can have up to 4G block

Q.Which Process writes data in Datafiles?

Database Writer background process DBWn (20 possible) writes dirty buffers from the buffer cache to the data files.
In other words, this process writes modified blocks permanently to disk.

Q.Can you make a Datafile auto extendible. If yes, how?

YES. A Datafile can be auto extendible.
Here’s how to enable auto extend on a Datafile:
SQL>alter database datafile ‘/u01/app/oracle/product/10.2.0/oradata/DBSID/EXAMPLE01.DBF’ autoextend on;
Note: For tablespaces defined with multiple data files (and partitioned table files), only the “last” data file needs the autoextend option.
SQL>spool runts.sql
SQL>select ‘alter database datafile ‘|| file_name|| ‘ ‘|| ‘ autoextend on;’ from dba_data_files;

Q.When a user process fails, what background process cleans up after it?


Q.Describe what redo logs are?

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.

Q.When creating a user, what permissions must you grant to allow them to connect to the database?

Grant the CONNECT to the user.