Explore topic-wise InterviewSolutions in .

database enforced integrity : Oracle ENFORCES data integrity, “BUSINESS rules” that dictate the standards for acceptable data. As a RESULT, the costs of CODING and managing checks in many database applications are eliminated.

database enforced integrity : Oracle enforces data integrity, “business rules” that dictate the standards for acceptable data. As a result, the costs of coding and managing checks in many database applications are eliminated.

At the finest level of granularity, an Oracle database’s data is stored in data blocks. ONE data block corresponds to a SPECIFIC number of BYTES of physical database space on disk. A data block size is SPECIFIED for each Oracle database when the database is CREATED. A database uses and allocates free database space in Oracle data blocks.

At the finest level of granularity, an Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. A data block size is specified for each Oracle database when the database is created. A database uses and allocates free database space in Oracle data blocks.

PROCESSES : A PROCESS is a “thread of CONTROL” or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms JOB or task. A process normally has its own PRIVATE memory area in which it runs.

Processes : A process is a “thread of control” or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.

LOG Writer (LGWR) The Log Writer WRITES redo log ENTRIES to disk. Redo log data is generated in the redo log buffer of the SYSTEM global area. As transactions commit and the log buffer FILLS, LGWR writes redo log entries into an online redo log file.

Log Writer (LGWR) The Log Writer writes redo log entries to disk. Redo log data is generated in the redo log buffer of the system global area. As transactions commit and the log buffer fills, LGWR writes redo log entries into an online redo log file.

Checkpoint (CKPT) At SPECIFIC times, all modified database buffers in the system global area are written to the datafiles by DBWR; this event is CALLED a checkpoint. The Checkpoint process is responsible for signalling DBWR at CHECKPOINTS and updating all the datafiles and control files of the database to indicate the most recent checkpoint. CKPT is optional; if CKPT is not PRESENT, LGWR assumes the responsibilities of CKPT.

Checkpoint (CKPT) At specific times, all modified database buffers in the system global area are written to the datafiles by DBWR; this event is called a checkpoint. The Checkpoint process is responsible for signalling DBWR at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint. CKPT is optional; if CKPT is not present, LGWR assumes the responsibilities of CKPT.

System Monitor (SMON) The system monitor performs instance recovery at instance STARTUP. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instances that have failed.

SMON also cleans up temporary SEGMENTS that are no longer in USE and recovers dead transactions skipped during CRASH and instance recovery because of file–read or offline errors.

These transactions are eventually recovered by SMON when the tablespace or file is brought back online. SMON also coalesces free extents within the database to make free space contiguous and easier to ALLOCATE.

System Monitor (SMON) The system monitor performs instance recovery at instance startup. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instances that have failed.

SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file–read or offline errors.

These transactions are eventually recovered by SMON when the tablespace or file is brought back online. SMON also coalesces free extents within the database to make free space contiguous and easier to allocate.

Process Monitor (PMON) The process monitor performs process recovery when a user process fails. PMON is responsible for CLEANING up the cache and freeing RESOURCES that the process was using. PMON ALSO checks on dispatcher (SEE below) and server PROCESSES and restarts them if they have failed.

Process Monitor (PMON) The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher (see below) and server processes and restarts them if they have failed.

The Program Interface : The program interface is the mechanism by which a user process COMMUNICATES with a server process. It SERVES as a METHOD of standard communication between any client tool or application (such as Oracle Forms) and Oracle SOFTWARE. 

The Program Interface : The program interface is the mechanism by which a user process communicates with a server process. It serves as a method of standard communication between any client tool or application (such as Oracle Forms) and Oracle software. 

An integrity constraint is a declarative way to define a business RULE for a column of a table. An integrity constraint is a statement about a table’s data that is always true:

• If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, the constraint cannot be enforced.
• After a constraint is defined, if any of the results of a DML statement violate the integrity constraint, the statement is rolled back and an error is returned.

Integrity constraints are defined with a table and are stored as part of the table’s definition, centrally in the database’s data dictionary, so that all database applications must adhere to the same set of rules. If a rule changes, it need only be changed once at the database level and not many times for each application.

The following integrity constraints are supported by Oracle:

• Disallows nulls (empty entries) in a table’s column.
• Disallows duplicate VALUES in a column or set of columns.
• Disallows duplicate values and nulls in a column or set of columns.
• Requires each value in a column or set of columns match a value in a related table’s UNIQUE or PRIMARY KEY (FOREIGN KEY integrity constraints also define referential integrity actions that dictate what Oracle should do with DEPENDENT data if the data it REFERENCES is altered).
• Disallows values that do not satisfy the logical expression of the constraint.

An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table’s data that is always true:

Integrity constraints are defined with a table and are stored as part of the table’s definition, centrally in the database’s data dictionary, so that all database applications must adhere to the same set of rules. If a rule changes, it need only be changed once at the database level and not many times for each application.

The following integrity constraints are supported by Oracle:

Security Mechanisms : The Oracle Server provides discretionary access CONTROL, which is a MEANS of RESTRICTING access to information BASED on privileges. The appropriate privilege must be assigned to a user in order for that user to access an object. Appropriately PRIVILEGED users can grant other users privileges at their discretion; for this reason, this type of security is called “discretionary”.

Oracle manages database security using several different facilities:

• database users and schemas
• privileges
• roles
• storage settings and quotas
• resource limits
• auditing

Security Mechanisms : The Oracle Server provides discretionary access control, which is a means of restricting access to information based on privileges. The appropriate privilege must be assigned to a user in order for that user to access an object. Appropriately privileged users can grant other users privileges at their discretion; for this reason, this type of security is called “discretionary”.

Oracle manages database security using several different facilities:

There are three STEPS to STARTING a database and MAKING it available for systemwide USE:

• Start an instance.
• Mount the database.
• Open the database.

There are three steps to starting a database and making it available for systemwide use:

There are three STEPS to SHUTTING down an instance and the database to which it is CONNECTED:

• Close the database.
• Dismount the database.
• Shut down the instance.

Oracle automatically PERFORMS all three steps when an instance is shut down.

There are three steps to shutting down an instance and the database to which it is connected:

Oracle automatically performs all three steps when an instance is shut down.

PARAMETER Files : To start an instance, Oracle must read a parameter file.A parameter file is a text file containing a LIST of instance configuration parameters. You SET these parameters to particular values and to initialize many of the memory and process settings of an Oracle instance. Among other things, the parameters of this file tell Oracle the following:

• the NAME of the database for which to start up an instance.
• how much memory to use for memory structures in the SGA.
• what to do with filled online redo log files.
• the names and LOCATIONS of the database’s control files.
• the names of private rollback segments in the database.

Parameter Files : To start an instance, Oracle must read a parameter file.A parameter file is a text file containing a list of instance configuration parameters. You set these parameters to particular values and to initialize many of the memory and process settings of an Oracle instance. Among other things, the parameters of this file tell Oracle the following:

Segments : A segment is a set of EXTENTS that contain all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates ONE or more extents to form that table’s data segment, and, for each index, Oracle allocates one or more extents to form its index segment.

There are FOUR types of segments used in Oracle DATABASES:

1. data segments.
2. index segments.
3. rollback segments.
4. temporary segments.

Segments : A segment is a set of extents that contain all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table’s data segment, and, for each index, Oracle allocates one or more extents to form its index segment.

There are four types of segments used in Oracle databases:

TEMPORARY Segments : When PROCESSING queries, ORACLE often requires temporary workspace for intermediate stages of SQL statement processing. Oracle AUTOMATICALLY allocates this DISK space called a temporary segment.Typically, Oracle requires a temporary segment as a work area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexes.

Temporary Segments : When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement processing. Oracle automatically allocates this disk space called a temporary segment.Typically, Oracle requires a temporary segment as a work area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexes.

The following commands may require the use of a temporary segment:

• CREATE INDEX
• SELECT ... ORDER BY
• SELECT DISTINCT ...
• SELECT ... GROUP BY
• SELECT ... UNION
• SELECT ... INTERSECT
• SELECT ... MINUS
• unindexed JOINS
• certain CORRELATED subqueries

The following commands may require the use of a temporary segment:

A null is the absence of a value in a column of a ROW. Nulls indicate MISSING, unknown, or INAPPLICABLE data. A null should not be used to imply any other value, such as zero. A column allows nulls unless a NOT NULL or PRIMARY KEY INTEGRITY constraint has been DEFINED for the column, in which case no row can be inserted without a value for that column.

A null is the absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data. A null should not be used to imply any other value, such as zero. A column allows nulls unless a NOT NULL or PRIMARY KEY integrity constraint has been defined for the column, in which case no row can be inserted without a value for that column.

1. General Rule Any INSERT, UPDATE, or DELETE OPERATION on a join view can modify only ONE underlying base table at a TIME.
2. UPDATE Rule All updatable columns of a join view must map to columns of a key preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non–updatable.
3. DELETE Rule Rows from a join view can be deleted as long as there is exactly one key–preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
4. INSERT Rule An INSERT statement must not, explicitly or implicitly, REFER to the columns of a non–key preserved table. If the join view is defined with the WITH CHECK OPTION clause, then INSERT statements are not PERMITTED.

The sequence generator provides a SEQUENTIAL series of numbers. The sequence generator is especially USEFUL in multi–user environments for generating unique sequential numbers without the overhead of disk I/O or transaction locking. Therefore, the sequence generator reduces “serialization” where the statements of two TRANSACTIONS must generate sequential numbers at the same time. By avoiding the serialization that results when MULTIPLE users wait for each other to generate and use a sequence number, the sequence generator improves transaction throughput and a user’s wait is CONSIDERABLY shorter.

The sequence generator provides a sequential series of numbers. The sequence generator is especially useful in multi–user environments for generating unique sequential numbers without the overhead of disk I/O or transaction locking. Therefore, the sequence generator reduces “serialization” where the statements of two transactions must generate sequential numbers at the same time. By avoiding the serialization that results when multiple users wait for each other to generate and use a sequence number, the sequence generator improves transaction throughput and a user’s wait is considerably shorter.

A COMPOSITE index (ALSO called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any ORDER and need not be adjacent in the table. Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index. THEREFORE, you should give some thought to the order of the columns used in the definition; generally, the most commonly accessed or most selective columns go first.

A composite index (also called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any order and need not be adjacent in the table. Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index. Therefore, you should give some thought to the order of the columns used in the definition; generally, the most commonly accessed or most selective columns go first.

INDEXES can be unique or non–unique. Unique indexes guarantee that no two rows of a table have duplicate VALUES in the columns that define the index. Non–unique indexes do not impose this restriction on the column values.

Oracle RECOMMENDS that you do not explicitly define unique indexes on TABLES; uniqueness is strictly a logical concept and should be associated with the definition of a table. Alternatively, define UNIQUE integrity constraints on the desired columns. Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key

Indexes can be unique or non–unique. Unique indexes guarantee that no two rows of a table have duplicate values in the columns that define the index. Non–unique indexes do not impose this restriction on the column values.

Oracle recommends that you do not explicitly define unique indexes on tables; uniqueness is strictly a logical concept and should be associated with the definition of a table. Alternatively, define UNIQUE integrity constraints on the desired columns. Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key

Clusters are an optional method of storing table data. A cluster is a GROUP of tables that share the same data blocks because they share common COLUMNS and are often USED together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT tables , ORACLE PHYSICALLY stores all rows for each department from both the EMP and DEPT tables in the same data blocks.

Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT tables , Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks.

The CLUSTER key is the COLUMN, or group of COLUMNS, that the clustered tables have in common. You specify the columns of the cluster key when CREATING the cluster. You subsequently specify the same columns when creating every table added to the cluster.

The cluster key is the column, or group of columns, that the clustered tables have in common. You specify the columns of the cluster key when creating the cluster. You subsequently specify the same columns when creating every table added to the cluster.

A hash function is a function applied to a cluster key value that returns a hash value. Oracle then uses the hash value to locate the row in the PROPER data block of the hash cluster. The job of a hash function is to provide the maximum distribution of ROWS among the available hash values of the cluster. To ACHIEVE this GOAL, a hash function must minimize the number of collisions.

A hash function is a function applied to a cluster key value that returns a hash value. Oracle then uses the hash value to locate the row in the proper data block of the hash cluster. The job of a hash function is to provide the maximum distribution of rows among the available hash values of the cluster. To achieve this goal, a hash function must minimize the number of collisions.

In some cases, ORACLE supplies DATA of one datatype where it expects data of a DIFFERENT datatype. This is ALLOWED when Oracle can automatically convert the data to the expected datatype using one of the following functions:

• TO_NUMBER()
• TO_CHAR()
• TO_DATE()
• TO_LABEL()
• CHARTOROWID()
• ROWIDTOCHAR()
• HEXTOCHAR()
• CHARTOHEX()

In some cases, Oracle supplies data of one datatype where it expects data of a different datatype. This is allowed when Oracle can automatically convert the data to the expected datatype using one of the following functions:

Oracle uses ROWIDs internally for the CONSTRUCTION of indexes. Each key in an index is associated with a ROWID that points to the associated row’s address for fast access.End–users and application developers can also USE ROWIDs for SEVERAL important uses:

• ROWIDs are the fastest means of accessing particular rows.
• ROWIDs can be used to see how a table is organized.
• ROWIDs are UNIQUE identifiers for rows in a given table.

Oracle uses ROWIDs internally for the construction of indexes. Each key in an index is associated with a ROWID that points to the associated row’s address for fast access.End–users and application developers can also use ROWIDs for several important uses:

The ORACLE datatypes :

1. CHAR
2. VARCHAR2
3. VARCHAR
4. NUMBER
5. DATE
6. LONG
7. RAW
8. LONG RAW.
9. ROWID
10. MLSLABEL.

The Oracle datatypes :