11). Which default Database roles are created when you create a Database ?

CONNECT , RESOURCE and DBA are three default roles. The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role.

The following query lists all the roles in the database:

SELECT * FROM DBA_ROLES;

ROLE PASSWORD

—————- ——–

CONNECT NO

RESOURCE NO

DBA NO

SECURITY_ADMIN YES

12). What is a Checkpoint ?

A checkpoint occurs when the DBWR (database writer) process writes all modified buffers in the SGA buffer cache to the database data files.

Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks. Checkpoints occur AFTER (not during) every redo log switch and also at intervals specified by initialization parameters.

Set parameter LOG_CHECKPOINTS_TO_ALERT=TRUE to observe checkpoint start and end times in the database alert log.

Checkpoints can be forced with the ALTER SYSTEM CHECKPOINT; command.


SCN can refer to:

System Change Number – A number, internal to Oracle that is incremented over time as change vectors are generated, applied, and written to the Redo log.

System Commit Number – A number, internal to Oracle that is incremented with each database COMMIT.

Note: System Commit Numbers and System Change Numbers share the same internal sequence generator.


13). Which Process reads data from Datafiles ?

Server Process – There is no background process which reads data from datafile or database buffer.

Oracle creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle to carry out requests from the associated user process. For example, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA.

Oracle can be configured to vary the number of user processes for each server process. 

In a dedicated server configuration, a server process handles requests for a single user process.

A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the use of available system resources.


14). 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.


15). 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;

SQL>@runts


16). What is a Shared Pool ?

The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE.

The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms.

Increasing the value of this parameter increases the amount of memory reserved for the shared pool.


17). What is kept in the Database Buffer Cache ?

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles.

All user processes concurrently connected to the instance share access to the database buffer cache.


18). How many maximum Redo Logfiles one can have in a Database ?

Maximum number of logfiles is limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement. Control file can be resized to allow more entries; ultimately an operating system limit. Maximum number of logfiles per group – Unlimited

Consider the parameters that can limit the number of redo log files before setting up or altering the configuration of an instance redo log.

The following parameters limit the number of redo log files that you can add to a database: MAXLOGFILES & MAXLOGMEMBERS.

The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of redo log files for each database. Group values can range from 1 to MAXLOGFILES.

When the compatibility level is set earlier than 10.2.0, the only way to override this upper limit is to re-create the database or its control file. Therefore, it is important to consider this limit before creating a database.

When compatibility is set to 10.2.0 or later, you can exceed the MAXLOGFILES limit, and the control files expand as needed.

If MAXLOGFILES is not specified for the CREATE DATABASE statement, then the database uses an operating system specific default value.

The MAXLOGMEMBERS parameter used in the CREATE DATABASE statement determines the maximum number of members for each group. As with MAXLOGFILES, the only way to override this upper limit is to re-create the database or control file. Therefore, it is important to consider this limit before creating a database.

If no MAXLOGMEMBERS parameter is specified for the CREATE DATABASE statement, then the database uses an operating system default value.


19). What is difference between PFile and SPFile ?

PFILE is a static, text file located in $ORACLE_HOME/dbs – UNIX

An SPFILE (Server Parameter File) is a persistent server-side binary file that can only be modified with the “ALTER SYSTEM SET” command.


20). What is PGA_AGGREGATE_TARGET parameter ?

PGA_AGGREGATE_TARGET: specifies the target aggregate PGA memory available to all server processes attached to the instance.