Q2

WHAT IS AN INSTANCE AND BACKGROUND PROCESSES

Oracle instance (database or ASM) has a unique system identifier (SID). To connect to an instance, you must at a minimum set the ORACLE_SID environment variable to the SID of that instance. Depending on the operating system, you may need to set other environment variables to properly change from one instance to another.
Oracle Database Background Processes
Process Name Description
Database writer (DBWn) The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle Database allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj). The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes. The database selects an appropriate default setting for this initialization parameter or adjusts a user-specified setting based on the number of CPUs and the number of processor groups.
.
Log writer (LGWR) The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the system global area (SGA). LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files .
Checkpoint (CKPT) At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.
System monitor (SMON) The system monitor performs recovery when a failed instance starts up again. In a Real Application Clusters database, the SMON process of one instance can 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 system failure 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.
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 the dispatcher processes (described later in this table) and server processes and restarts them if they have failed.
Archiver (ARCn) One or more archiver processes copy the redo log files to archival storage when they are full or a log switch occurs .
Recoverer (RECO) The recoverer process is used to resolve distributed transactions that are pending because of a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions. For information about this process and how to start it
Dispatcher (Dnnn) Dispatchers are optional background processes, present only when the shared server configuration is used. .
Global Cache Service (LMS) In a Real Application Clusters environment, this process manages resources and provides inter-instance resource control.

















RELEASE NUMBER FORMAT
To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".
Figure 1-1 Example of an Oracle Database Release Number

Description of the illustration admin002.gif

Note:
Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number
The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.
Database Maintenance Release Number
The second digit represents a maintenance release level. Some new features may also be included.
Application Server Release Number
The third digit reflects the release level of the Oracle Application Server (OracleAS).
Component-Specific Release Number
The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
Platform-Specific Release Number
The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.
Checking Your Current Release Number
To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the database server.
COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT VERSION STATUS
---------------------------------------- ----------- -----------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod
PL/SQL 10.2.0.1.0 Production
...

It is important to convey to Oracle the results of this query when you report problems with the software.
FILE STRUCTURES IN ORACLE


PACKAGES, PACKAGED VARIABLES AND SCOPE, ADVANTAGES, OVERLOADING
A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.
The spec holds public declarations, which are visible to stored procedures and other code outside the package. You must declare subprograms at the end of the spec after all other items (except pragmas that name a specific function; such pragmas must follow the function spec).
The body holds implementation details and private declarations, which are hidden from code outside the package. Following the declarative part of the package body is the optional initialization part, which holds statements that initialize package variables and do any other one-time setup steps.
Advantages of PL/SQL Packages
Packages have a long history in software engineering, offering important features for reliable, maintainable, reusable code, often in team development efforts for large systems.
Modularity
Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.
Easier Application Design
When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.
Information Hiding
With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.
Added Functionality
Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database.

Better Performance
When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.
Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the body of a packaged function, Oracle does not recompile other subprograms that call the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes.
Private Versus Public Items in Packages
In the package emp_admin, the package body declares a variable named number_hired, which is initialized to zero. Items declared in the body are restricted to use within the package. PL/SQL code outside the package cannot reference the variable number_hired. Such items are called private.
Items declared in the spec of emp_admin, such as the exception invalid_salary, are visible outside the package. Any PL/SQL code can reference the exception invalid_salary. Such items are called public.
To maintain items throughout a session or across transactions, place them in the declarative part of the package body. For example, the value of number_hired is kept between calls to hire_employee within the same session. The value is lost when the session ends.
To make the items public, place them in the package specification. For example, emp_rec declared in the spec of the package is available for general use.
PL/SQL lets you overload subprogram names and type methods. You can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family. For an example of an overloaded procedure in a package, see Example 9-3.
Example 8-8 shows how you can define two subprograms with the same name. The procedures initialize different types of collections. Because the processing in these two procedures is the same, it is logical to give them the same name.
You can place the two overloaded initialize procedures in the same block, subprogram, package, or object type. PL/SQL determines which procedure to call by checking their formal parameters. The version of initialize that PL/SQL uses depends on whether you call the procedure with a DateTabTyp or NumTabTyp parameter.
Example 8-8 Overloading a Subprogram Name
DECLARE
TYPE DateTabTyp IS TABLE OF DATE INDEX BY PLS_INTEGER;
TYPE NumTabTyp IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
hiredate_tab DateTabTyp;
sal_tab NumTabTyp;
PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := SYSDATE;
END LOOP;
END initialize;
PROCEDURE initialize (tab OUT NumTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := 0.0;
END LOOP;
END initialize;
BEGIN
initialize(hiredate_tab, 50); -- calls first (DateTabTyp) version
initialize(sal_tab, 100); -- calls second (NumTabTyp) version
END;




DIFFERENCES BETWEEN VIEW, SYNONYMS
Synonyms
A synonym is an alias for any table, view, materialized view, sequence, procedure, function, or package. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.
Synonyms are often used for security and convenience. For example, they can do the following:
• Mask the name and owner of an object
• Provide location transparency for remote objects of a distributed database
• Simplify SQL statements for database users
• Enable restricted access similar to specialized views when exercising fine-grained access control
You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.
Synonyms are very useful in both distributed and nondistributed database environments because they hide the identity of the underlying object, including its location in a distributed system. This is advantageous because if the underlying object must be renamed or moved, then only the synonym needs to be redefined. Applications based on the synonym continue to function without modification.
Synonyms can also simplify SQL statements for users in a distributed database system. The following example shows how and why public synonyms are often created by a database administrator to hide the identity of a base table and reduce the complexity of SQL statements. Assume the following:
• A table called SALES_DATA is in the schema owned by the user JWARD.
• The SELECT privilege for the SALES_DATA table is granted to PUBLIC.
At this point, you have to query the table SALES_DATA with a SQL statement similar to the following:
SELECT * FROM jward.sales_data;

Notice how you must include both the schema that contains the table along with the table name to perform the query.
Assume that the database administrator creates a public synonym with the following SQL statement:
CREATE PUBLIC SYNONYM sales FOR jward.sales_data;

After the public synonym is created, you can query the table SALES_DATA with a simple SQL statement:
SELECT * FROM sales;

Notice that the public synonym SALES hides the name of the table SALES_DATA and the name of the schema that contains the table.
Views
A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used.
For example, the employees table has several columns and numerous rows of information. If you want users to see only five of these columns or only specific rows, then you can create a view of that table for other users to access.
How Views are Stored
Unlike a table, a view is not allocated any storage space, nor does a view actually contain data. Rather, a view is defined by a query that extracts or derives data from the tables that the view references. These tables are called base tables. Base tables can in turn be actual tables or can be views themselves (including materialized views). Because a view is based on other objects, a view requires no storage other than storage for the definition of the view (the stored query) in the data dictionary.
How Views Are Used
Views provide a means to present a different representation of the data that resides within the base tables. Views are very powerful because they let you tailor the presentation of data to different types of users. Views are often used to:
• Provide an additional level of table security by restricting access to a predetermined set of rows or columns of a table
For example, Figure 10-5 shows how the STAFF view does not show the salary or commission_pct columns of the base table employees.
• Hide data complexity
For example, a single view can be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables.
• Simplify statements for the user
For example, views allow users to select information from multiple tables without actually knowing how to perform a join.
• Present the data in a different perspective from that of the base table
For example, the columns of a view can be renamed without affecting the tables on which the view is based.
• Isolate applications from changes in definitions of base tables
For example, if a view's defining query references three columns of a four column table, and a fifth column is added to the table, then the view's definition is not affected, and all applications using the view are not affected.
• Express a query that cannot be expressed without using a view
For example, a view can be defined that joins a GROUP BY view with a table, or a view can be defined that joins a UNION view with a table.
• Save complex queries
For example, a query can perform extensive calculations with table information. By saving this query as a view, you can perform the calculations each time the view is queried.
Mechanics of Views
Oracle stores a view's definition in the data dictionary as the text of the query that defines the view. When you reference a view in a SQL statement, Oracle:
1. Merges the statement that references the view with the query that defines the view
2. Parses the merged statement in a shared SQL area
3. Executes the statement
Oracle parses a statement that references a view in a new shared SQL area only if no existing shared SQL area contains a similar statement. Therefore, you get the benefit of reduced memory use associated with shared SQL when you use views.
Dependencies and Views
Because a view is defined by a query that references other objects (tables, materialized views, or other views), a view depends on the referenced objects. Oracle automatically handles the dependencies for views. For example, if you drop a base table of a view and then create it again, Oracle determines whether the new base table is acceptable to the existing definition of the view.
TYPES OF TRIGGERS, MUTATING OF TRIGGERS
Types of Triggers
This section describes the different types of triggers:
• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events
Row Triggers and Statement Triggers
When you define a trigger, you can specify the number of times the trigger action is to be run:
• Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows
• Once for the triggering statement, no matter how many rows it affects
Row Triggers
A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.
Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 17-3 illustrates a row trigger that uses the values of each row affected by the triggering statement.
Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.
Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:
• Make a complex security check on the current time or user
• Generate a single audit record
BEFORE and AFTER Triggers
When defining a trigger, you can specify the trigger timing--whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.
BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.
BEFORE Triggers
BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the following situations:
• When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
• To derive specific column values before completing a triggering INSERT or UPDATE statement.
AFTER Triggers
AFTER triggers run the trigger action after the triggering statement is run.
Trigger Type Combinations
Using the options listed previously, you can create four types of row and statement triggers:
• BEFORE statement trigger
Before executing the triggering statement, the trigger action is run.
• BEFORE row trigger
Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.
• AFTER row trigger
After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
• AFTER statement trigger
After executing the triggering statement and applying any deferred integrity constraints, the trigger action is run.
You can have multiple triggers of the same type for the same statement for any given table. For example, you can have two BEFORE statement triggers for UPDATE statements on the employees table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle materialized view logs use AFTER row triggers, so you can design your own AFTER row trigger in addition to the Oracle-defined AFTER row trigger.
You can create as many triggers of the preceding different types as you need for each type of DML statement, (INSERT, UPDATE, or DELETE).
For example, suppose you have a table, SAL, and you want to know when the table is being accessed and the types of queries being issued. The following example contains a sample package and trigger that tracks this information by hour and type of action (for example, UPDATE, DELETE, or INSERT) on table SAL. The global session variable STAT.ROWCNT is initialized to zero by a BEFORE statement trigger. Then it is increased each time the row trigger is run. Finally the statistical information is saved in the table STAT_TAB by the AFTER statement trigger.



INSTEAD OF Triggers
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. Instead of Trigger is Statement level Trigger.
You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.
LOCKING ,AND TYPES OF LOCKING
Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource--either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows.
In all cases, Oracle automatically obtains necessary locks when executing SQL statements, so users need not be concerned with such details. Oracle automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. Oracle also allows the user to lock data manually.
Types of Locks
Oracle automatically uses different types of locks to control concurrent access to data and to prevent destructive interaction between users. Oracle automatically locks a resource on behalf of a transaction to prevent other transactions from doing something also requiring exclusive access to the same resource. The lock is released automatically when some event occurs so that the transaction no longer requires the resource.
Throughout its operation, Oracle automatically acquires different types of locks at different levels of restrictiveness depending on the resource being locked and the operation being performed.
Oracle locks fall into one of three general categories.
Lock Description
DML locks (data locks) DML locks protect data. For example, table locks lock entire tables, row locks lock selected rows.
DDL locks (dictionary locks) DDL locks protect the structure of schema objects--for example, the definitions of tables and views.
Internal locks and latches Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.

DML Locks
The purpose of a DML (data) lock is to guarantee the integrity of data being accessed concurrently by multiple users. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations. For example, Oracle DML locks guarantee that a specific row in a table can be updated by only one transaction at a time and that a table cannot be dropped if an uncommitted transaction contains an insert into the table.
DML operations can acquire data locks at two different levels: for specific rows and for entire tables.
Row Locks (TX)
The only DML locks Oracle acquires automatically are row-level locks. There is no limit to the number of row locks held by a statement or transaction, and Oracle does not escalate locks from the row level to a coarser granularity. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.
The combination of multiversion concurrency control and row-level locking means that users contend for data only when accessing the same rows, specifically:

• Readers of data do not wait for writers of the same data rows.
• Writers of data do not wait for readers of the same data rows unless SELECT ... FOR UPDATE is used, which specifically requests a lock for the reader.
• Writers only wait for other writers if they attempt to update the same rows at the same time.
A transaction acquires an exclusive DML lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.
A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back. However, if the transaction dies due to instance failure, block-level recovery makes a row available before the entire transaction is recovered. Row locks are always acquired automatically by Oracle as a result of the statements listed previously.
If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.
Table Locks (TM)
A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. These DML operations require table locks for two purposes: to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. Any table lock prevents the acquisition of an exclusive DDL lock on the same table and thereby prevents DDL operations that require such locks. For example, a table cannot be altered or dropped if an uncommitted transaction holds a table lock for it.
A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held.
Table 20-3 shows the table lock modes that statements acquire and operations that those locks permit and prohibit.
Table 20-3 Summary of Table Locks
SQL Statement Mode of Table Lock Lock Modes Permitted?
RS RX S SRX X
SELECT...FROM table... none Y Y Y Y Y
INSERT INTO table ... RX Y Y N N N
UPDATE table ... RX Y* Y* N N N
DELETE FROM table ... RX Y* Y* N N N
SELECT ... FROM table FOR UPDATE OF ... RS Y* Y* Y* Y* N
LOCK TABLE table IN ROW SHARE MODE RS Y Y Y Y N
LOCK TABLE table IN ROW EXCLUSIVE MODE RX Y Y N N N
LOCK TABLE table IN SHARE MODE S Y N Y N N
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE SRX Y N N N N
LOCK TABLE table IN EXCLUSIVE MODE X N N N N N

RS: row share
RX: row exclusive
S: share
SRX: share row exclusive
X: exclusive
*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.
The following sections explain each mode of table lock, from least restrictive to most restrictive. They also describe the actions that cause the transaction to acquire a table lock in that mode and which actions are permitted and prohibited in other transactions by a lock in that mode.
Row Share Table Locks (RS)
A row share table lock (also sometimes called a subshare table lock, SS) indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share table lock is automatically acquired for a table when one of the following SQL statements is executed:
SELECT ... FROM table ... FOR UPDATE OF ... ;

LOCK TABLE table IN ROW SHARE MODE;

A row share table lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.
Permitted Operations: A row share table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, other transactions can obtain simultaneous row share, row exclusive, share, and share row exclusive table locks for the same table.
Prohibited Operations: A row share table lock held by a transaction prevents other transactions from exclusive write access to the same table using only the following statement:
LOCK TABLE table IN EXCLUSIVE MODE;
Row Exclusive Table Locks (RX)
A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements:
INSERT INTO table ... ;

UPDATE table ... ;

DELETE FROM table ... ;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

A row exclusive table lock is slightly more restrictive than a row share table lock.
Permitted Operations: A row exclusive table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.
Prohibited Operations: A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:
LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;


Share Table Locks (S)
A share table lock is acquired automatically for the table specified in the following statement:
LOCK TABLE table IN SHARE MODE;

Permitted Operations: A share table lock held by a transaction allows other transactions only to query the table, to lock specific rows with SELECT ... FOR UPDATE, or to execute LOCK TABLE ... IN SHARE MODE statements successfully. No updates are allowed by other transactions. Multiple transactions can hold share table locks for the same table concurrently. In this case, no transaction can update the table (even if a transaction holds row locks as the result of a SELECT statement with the FOR UPDATE clause). Therefore, a transaction that has a share table lock can update the table only if no other transactions also have a share table lock on the same table.
Prohibited Operations: A share table lock held by a transaction prevents other transactions from modifying the same table and from executing the following statements:
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

LOCK TABLE table IN ROW EXCLUSIVE MODE;
Share Row Exclusive Table Locks (SRX)
A share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) is more restrictive than a share table lock. A share row exclusive table lock is acquired for a table as follows:
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

Permitted Operations: Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows using SELECT with the FOR UPDATE clause, but not to update the table.
Prohibited Operations: A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive table locks and modifying the same table. A share row exclusive table lock also prohibits other transactions from obtaining share, share row exclusive, and exclusive table locks, which prevents other transactions from executing the following statements:
LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;
Exclusive Table Locks (X)
An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:
LOCK TABLE table IN EXCLUSIVE MODE;

Permitted Operations: Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.
Prohibited Operations: An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.
DML Locks Automatically Acquired for DML Statements
The previous sections explained the different types of data locks, the modes in which they can be held, when they can be obtained, when they are obtained, and what they prohibit. The following sections summarize how Oracle automatically locks data on behalf of different DML operations.
Table 20-4 summarizes the information in the following sections.
Table 20-4 Locks Obtained By DML Statements
DML Statement Row Locks? Mode of Table Lock
SELECT ... FROM table
INSERT INTO table ... X RX
UPDATE table ... X RX
DELETE FROM table ... X RX
SELECT ... FROM table ... FOR UPDATE OF ... X RS
LOCK TABLE table IN ...
ROW SHARE MODE RS
ROW EXCLUSIVE MODE RX
SHARE MODE S
SHARE EXCLUSIVE MODE SRX
EXCLUSIVE MODE X

X: exclusive
RX: row exclusive
RS: row share
S: share
SRX: share row exclusive
Default Locking for Queries
Queries are the SQL statements least likely to interfere with other SQL statements because they only read data. INSERT, UPDATE, and DELETE statements can have implicit queries as part of the statement. Queries include the following kinds of statements:
SELECT

INSERT ... SELECT ... ;

UPDATE ... ;

DELETE ... ;

They do not include the following statement:
SELECT ... FOR UPDATE OF ... ;

The following characteristics are true of all queries that do not use the FOR UPDATE clause:
• A query acquires no data locks. Therefore, other transactions can query and update a table being queried, including the specific rows being queried. Because queries lacking FOR UPDATE clauses do not acquire any data locks to block other operations, such queries are often referred to in Oracle as nonblocking queries.
• A query does not have to wait for any data locks to be released; it can always proceed. (Queries may have to wait for data locks in some very specific cases of pending distributed transactions.)
Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE
The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE statements are as follows:
• The transaction that contains a DML statement acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.
• The transaction that contains a DML statement does not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a WHERE clause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.
• A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.
• In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction already holds a row share table lock, Oracle automatically converts this lock to a row exclusive table lock.
DDL Locks
A data dictionary lock (DDL) protects the definition of a schema object while that object is acted upon or referred to by an ongoing DDL operation. Recall that a DDL statement implicitly commits its transaction. For example, assume that a user creates a procedure. On behalf of the user's single-statement transaction, Oracle automatically acquires DDL locks for all schema objects referenced in the procedure definition. The DDL locks prevent objects referenced in the procedure from being altered or dropped before the procedure compilation is complete.
Oracle acquires a dictionary lock automatically on behalf of any DDL transaction requiring it. Users cannot explicitly request DDL locks. Only individual schema objects that are modified or referenced are locked during DDL operations. The whole data dictionary is never locked.
DDL locks fall into three categories: exclusive DDL locks, share DDL locks, and breakable parse locks.
Exclusive DDL Locks
Most DDL operations, except for those listed in the next section, "Share DDL Locks", require exclusive DDL locks for a resource to prevent destructive interference with other DDL operations that might modify or reference the same schema object. For example, a DROP TABLE operation is not allowed to drop a table while an ALTER TABLE operation is adding a column to it, and vice versa.
During the acquisition of an exclusive DDL lock, if another DDL lock is already held on the schema object by another operation, the acquisition waits until the older DDL lock is released and then proceeds.
DDL operations also acquire DML locks (data locks) on the schema object to be modified.
Share DDL Locks
Some DDL operations require share DDL locks for a resource to prevent destructive interference with conflicting DDL operations, but allow data concurrency for similar DDL operations. For example, when a CREATE PROCEDURE statement is executed, the containing transaction acquires share DDL locks for all referenced tables. Other transactions can concurrently create procedures that reference the same tables and therefore acquire concurrent share DDL locks on the same tables, but no transaction can acquire an exclusive DDL lock on any referenced table. No transaction can alter or drop a referenced table. As a result, a transaction that holds a share DDL lock is guaranteed that the definition of the referenced schema object will remain constant for the duration of the transaction.
A share DDL lock is acquired on a schema object for DDL statements that include the following statements: AUDIT, NOAUDIT, COMMENT, CREATE [OR REPLACE] VIEW/ PROCEDURE/PACKAGE/PACKAGE BODY/FUNCTION/ TRIGGER, CREATE SYNONYM, and CREATE TABLE (when the CLUSTER parameter is not included).


Breakable Parse Locks
A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock.
A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool.
FLASHBACK QUERIES AND ADVANTAGES
Flashback Query
Flashback query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system commit number (SCN).
Flashback query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Administrators can configure undo retention by simply specifying how long undo should be kept in the database. Using flashback query, a user can query the database as it existed this morning, yesterday, or last week. The speed of this operation depends only on the amount of data being queried and the number of changes to the data that need to be backed out.
You set the date and time you want to view. Then, any SQL query you execute operates on data as it existed at that time. If you are an authorized user, then you can correct errors and back out the restored data without needing the intervention of an administrator.
With the AS OF SQL clause, you can choose different snapshots for each table in the query. Associating a snapshot with a table is known as table decoration. If you do not decorate a table with a snapshot, then a default snapshot is used for it. All tables without a specified snapshot get the same default snapshot.
For example, suppose you want to write a query to find all the new customer accounts created in the past hour. You could do set operations on two instances of the same table decorated with different AS OF clauses.
DML and DDL operations can use table decoration to choose snapshots within subqueries. Operations such as INSERT TABLE AS SELECT and CREATE TABLE AS SELECT can be used with table decoration in the subqueries to repair tables from which rows have been mistakenly deleted. Table decoration can be any arbitrary expression: a bind variable, a constant, a string, date operations, and so on. You can open a cursor and dynamically bind a snapshot value (a timestamp or an SCN) to decorate a table with. Flashback Query Benefits
• Application Transparency
Packaged applications, like report generation tools that only do queries, can run in flashback query mode by using logon triggers. Applications can run transparently without requiring changes to code. All the constraints that the application needs to be satisfied are guaranteed to hold good, because ther is a consistent version of the database as of the flashback query time.
• Application Performance
If an application requires recovery actions, it can do so by saving SCNs and flashing back to those SCNs. This is a lot easier and faster than saving data sets and restoring them later, which would be required if the application were to do explicit versioning. Using flashback query, there are no costs for logging that would be incurred by explicit versioning.
• Online Operation
Flashback query is an online operation. Concurrent DMLs and queries from other sessions are permitted while an object is being queried inside flashback query.The speed of these operations is unaffected. Moreover, different sessions can flash back to different flashback times or SCNs on the same object concurrently. The speed of the flashback query itself depends on the amount of undo that needs to be applied, which is proportional to how far back in time the query goes.


• Easy Manageability
There is no additional management on the part of the user, except setting the appropriate retention interval, having the right privileges, and so on. No additional logging has to be turned on, because past versions are constructed automatically, as needed.
________________________________________
Notes:
• Flashback query does not undo anything. It is only a query mechanism. You can take the output from a flashback query and perform an undo yourself in many circumstances.
• Flashback query does not tell you what changed. LogMiner does that.
• Flashback query can be used to undo changes and can be very efficient if you know the rows that need to be moved back in time. You can in theory use it to move a full table back in time but this is very expensive if the table is large since it involves a full table copy.
• Flashback query does not work through DDL operations that modify columns, or drop or truncate tables.
• LogMiner is very good for getting change history, but it gives you changes in terms of deltas (insert, update, delete), not in terms of the before and after image of a row. These can be difficult to deal with in some applications.
________________________________________

AUTONOMOUS TRANSACTION AND ITS USAGE WITH EXAMPLE
Autonomous Transactions
Autonomous transactions are independent transactions that can be called from within another transaction. An autonomous transaction lets you leave the context of the calling transaction, perform some SQL operations, commit or roll back those operations, and then return to the calling transaction's context and continue with that transaction.
Once invoked, an autonomous transaction is totally independent of the main transaction that called it. It does not see any of the uncommitted changes made by the main transaction and does not share any locks or resources with the main transaction. Changes made by an autonomous transaction become visible to other transactions upon commit of the autonomous transactions.
One autonomous transaction can call another. There are no limits, other than resource limits, on how many levels of autonomous transactions can be called.
Deadlocks are possible between an autonomous transaction and its calling transaction. Oracle detects such deadlocks and returns an error. The application developer is responsible for avoiding deadlock situations.
Autonomous transactions are useful for implementing actions that need to be performed independently, regardless of whether the calling transaction commits or rolls back, such as transaction logging and retry counters.
Autonomous PL/SQL Blocks
You can call autonomous transactions from within a PL/SQL block. Use the pragma AUTONOMOUS_TRANSACTION. A pragma is a compiler directive. You can declare the following kinds of PL/SQL blocks to be autonomous:
• Stored procedure or function
• Local procedure or function
• Package
• Type method
• Top-level anonymous block
When an autonomous PL/SQL block is entered, the transaction context of the caller is suspended. This operation ensures that SQL operations performed in this block (or other blocks called from it) have no dependence or effect on the state of the caller's transaction context.
When an autonomous block invokes another autonomous block or itself, the called block does not share any transaction context with the calling block. However, when an autonomous block invokes a non-autonomous block (that is, one that is not declared to be autonomous), the called block inherits the transaction context of the calling autonomous block.
Advantages of Autonomous Transactions
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back.
More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous transactions on their own. A calling application need not know about a procedure's autonomous operations, and the procedure need not know about the application's transaction context. That makes autonomous transactions less error-prone than regular transactions and easier to use.
Furthermore, autonomous transactions have all the functionality of regular transactions. They allow parallel queries, distributed processing, and all the transaction control statements including SET TRANSACTION.
In the following example, you mark a packaged function as autonomous:
CREATE PACKAGE banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;

CREATE PACKAGE BODY banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN
...
END;
END banking;
Restriction: You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. For example, the following pragma is not allowed:
CREATE PACKAGE banking AS
PRAGMA AUTONOMOUS_TRANSACTION; -- not allowed
...
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;

In the next example, you mark a standalone procedure as autonomous:
CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN ... END;
In the following example, you mark a PL/SQL block as autonomous:
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
my_empno NUMBER(4);
BEGIN ... END;
Restriction: You cannot mark a nested PL/SQL block as autonomous.
In the example below, you mark a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK.
CREATE TRIGGER parts_trigger
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT; -- allowed only in autonomous triggers
END;

BULK BINDING , WITH SYNTAX WHY IT IS USED

Reducing Loop Overhead for Collections with Bulk Binds

the PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine.
Too many context switches between the PL/SQL and SQL engines can harm performance. That can happen when a loop executes a separate SQL statement for each element of a collection, specifying the collection element as a bind variable. For example, the following DELETE statement is sent to the SQL engine with each iteration of the FOR loop:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
...
FOR i IN depts.FIRST..depts.LAST LOOP
DELETE FROM emp WHERE deptno = depts(i);
END LOOP;
END;
In such cases, if the SQL statement affects four or more database rows, the use of bulk binds can improve performance considerably.
How Do Bulk Binds Improve Performance?
The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:
• in-bind When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
• out-bind When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
• define When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.
A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.
To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement.
To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

PRAGMA EXCEPTIONS, WHY IT IS USED
Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT
To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

where exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in the following example:
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
... -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN deadlock_detected THEN
-- handle the error
END;

REF CURSOR WHY IT IS USED
Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, cursors differ from cursor variables the way constants differ from variables. Whereas a cursor is static, a cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query. This gives you more flexibility.
Also, you can assign new values to a cursor variable and pass it as a parameter to local and stored subprograms. This gives you an easy way to centralize data retrieval.
Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Moreover, application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side.
The Oracle server also has a PL/SQL engine. So, you can pass cursor variables back and forth between an application and server through remote procedure calls (RPCs).
What Are Cursor Variables?
Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR.
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area. Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. So, cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected.

Why Use Cursor Variables?
Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle server can all refer to the same work area.
A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.
If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. Also, you can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.
Defining REF CURSOR Types
To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax
TYPE ref_type_name IS REF CURSOR [RETURN return_type];

where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent a record or a row in a database table. In the following example, you specify a return type that represents a row in the database table dept:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). As the next example shows, a strong REF CURSOR type definition specifies a return type, but a weak definition does not:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak

Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.
DYNAMIC SQL
What Is Dynamic SQL?
Most PL/SQL programs do a specific, predictable job. For example, a stored procedure might accept an employee number and salary increase, then update the sal column in the emp table. In this case, the full text of the UPDATE statement is known at compile time. Such statements do not change from execution to execution. So, they are called static SQL statements.
However, some programs must build and process a variety of SQL statements at run time. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the full text of the statement is unknown until run time. Such statements can, and probably will, change from execution to execution. So, they are called dynamic SQL statements.
Dynamic SQL statements are stored in character strings built by your program at run time. Such strings must contain the text of a valid SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. A placeholder is an undeclared identifier, so its name, to which you must prefix a colon, does not matter. For example, PL/SQL makes no distinction between the following strings:
'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. However, to process a multi-row query (SELECT statement), you must use the OPEN-FOR, FETCH, and CLOSE statements.
The Need for Dynamic SQL
You need dynamic SQL in the following situations:
• You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). In PL/SQL, such statements cannot be executed statically.
• You want more flexibility. For example, you might want to defer your choice of schema objects until run time. Or, you might want your program to build different search conditions for the WHERE clause of a SELECT statement. A more complex program might choose from various SQL operations, clauses, etc.
• You use package DBMS_SQL to execute SQL statements dynamically, but you want better performance, something easier to use, or functionality that DBMS_SQL lacks such as support for objects and collections.

OBJECT ORIENTED FEATURE IN ORACLE

Introduction to Object Datatypes
Relational database management systems (RDBMSs) are the standard tool for managing business data. They provide reliable access to huge amounts of data for millions of businesses around the world every day.
Oracle is an object-relational database management system (ORDBMS), which means that users can define additional kinds of data--specifying both the structure of the data and the ways of operating on it--and use these types within the relational model. This approach adds value to the data stored in a database. Object datatypes make it easier for application developers to work with complex data such as images, audio, and video. Object types store structured business data in its natural form and allow applications to retrieve it that way. For that reason, they work efficiently with applications developed using object-oriented programming techniques.
Complex Data Models
The Oracle server lets you define complex business models in SQL and make them part of your database schema. Applications that manage and share your data need only contain the application logic, not the data logic.
Collection Types
Each collection type describes a data unit made up of an indefinite number of elements, all of the same datatype. The collection types are array types and table types.
Array types and table types are schema objects. The corresponding data units are called VARRAYs and nested tables. When there is no danger of confusion, we often refer to the collection types as VARRAYs and nested tables.
Collection types have constructor methods. The name of the constructor method is the name of the type, and its argument is a comma separated list of the new collection's elements. The constructor method is a function. It returns the new collection as its value.
An expression consisting of the type name followed by empty parentheses represents a call to the constructor method to create an empty collection of that type. An empty collection is different from a null collection.
VARRAYs
An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.
The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called VARRAYs. You must specify a maximum size when you declare the array type.
For example, the following statement declares an array type:
CREATE TYPE prices AS VARRAY(10) OF NUMBER(12,2);

The VARRAYs of type prices have no more than 10 elements, each of datatype NUMBER(12,2).
Creating an array type does not allocate space. It defines a datatype, which you can use as:
• The datatype of a column of a relational table
• An object type attribute
• A PL/SQL variable, parameter, or function return type.
A VARRAY is normally stored in line; that is, in the same tablespace as the other data in its row. If it is sufficiently large, however, Oracle stores it as a BLOB.
A nested table is an unordered set of data elements, all of the same datatype. It has a single column, and the type of that column is a built-in type or an object type. If an object type, the table can also be viewed as a multicolumn table, with a column for each attribute of the object type. If compatibility is set to Oracle9i or higher, nested tables can contain other nested tables.
For example, in the purchase order example, the following statement declares the table type used for the nested tables of line items:
CREATE TYPE lineitem_table AS TABLE OF lineitem;

A table type definition does not allocate space. It defines a type, which you can use as:
• The datatype of a column of a relational table
• An object type attribute
• A PL/SQL variable, parameter, or function return type
When a table type appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single table, which it associates with the enclosing relational or object table. For example, the following statement defines an object table for the object type purchase_order:
CREATE TABLE purchase_order_table OF purchase_order
NESTED TABLE lineitems STORE AS lineitems_table;

The second line specifies lineitems_table as the storage table for the lineitems attributes of all of the purchase_order objects in purchase_order_table.
A convenient way to access the elements of a nested table individually is to use a nested cursor.

SCALAR DATA TYPES
A datatype is either scalar or nonscalar. A scalar type contains an atomic value, whereas a nonscalar (sometimes called a "collection") contains a set of values. A large object (LOB) is a special form of scalar datatype representing a large scalar value of binary or character data. LOBs are subject to some restrictions that do not affect other scalar types because of their size. Those restrictions are documented in the context of the relevant SQL syntax.





TYPES OF INDEXES
Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. Indexes are the primary means of reducing disk I/O when properly used.
Oracle provides several indexing schemes, which provide complementary performance functionality:
• B-tree indexes
• B-tree cluster indexes
• Hash cluster indexes
• Reverse key indexes
• Bitmap indexes
• Bitmap Join Indexes
Unique and Nonunique Indexes
Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values.
Oracle recommends that unique indexes be created explicitly, and not through enabling a unique constraint on a table.
Alternatively, you can define UNIQUE integrity constraints on the desired columns. Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.
Composite Indexes
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, the order of the columns used in the definition is important. Generally, the most commonly accessed or most selective columns go first.
You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of the function or expression and stores it in the index. You can create a function-based index as either a B-tree or a bitmap index.
The function used for building the index can be an arithmetic expression or an expression that contains a PL/SQL function, package function, C callout, or SQL function. The expression cannot contain any aggregate functions, and it must be DETERMINISTIC. For building an index on a column containing an object type, the function can be a method of that object, such as a map method. However, you cannot build a function-based index on a LOB column, REF, or nested table column, nor can you build a function-based index if the object type contains a LOB, REF, or nested table.
Uses of Function-Based Indexes
Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses. The value of the expression is computed and stored in the index. When it processes INSERT and UPDATE statements, however, Oracle must still evaluate the function to process the statement.




For example, if you create the following index:
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);
then Oracle can use it when processing queries such as this:
SELECT a FROM table_1 WHERE a + b * (c - 1) < 100;
Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. For example, the following index:
CREATE INDEX uppercase_idx ON employees (UPPER(first_name));
can facilitate processing queries such as this:
SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD';

A function-based index can also be used for a globalization support sort index that provides efficient linguistic collation in SQL statements.
Advantages of B-tree Structure
The B-tree structure has the following advantages:
• All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time.
• B-tree indexes automatically stay balanced.
• All blocks of the B-tree are three-quarters full on the average.
• B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
• Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
• B-tree performance is good for both small and large tables and does not degrade as the size of a table grows.
Reverse Key Indexes
Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation with Oracle9i Real Application Clusters where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.
Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Because lexically adjacent keys are not stored next to each other in a reverse-key index, only fetch-by-key or full-index (table) scans can be performed.
Sometimes, using a reverse-key index can make an OLTP Oracle9i Real Application Clusters application faster. For example, keeping the index of mail messages in an e-mail application: some users keep old messages, and the index must maintain pointers to these as well as to the most recent.
The REVERSE keyword provides a simple mechanism for creating a reverse key index. You can specify the keyword REVERSE along with the optional index specifications in a CREATE INDEX statement:
CREATE INDEX i ON t (a,b,c) REVERSE;


You can specify the keyword NOREVERSE to REBUILD a reverse-key index into one that is not reverse keyed:
ALTER INDEX i REBUILD NOREVERSE;

Rebuilding a reverse-key index without the NOREVERSE keyword produces a rebuilt, reverse-key index.
Bitmap Indexes
The purpose of an index is to provide pointers to the rows in a table that contain a given key value. In a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. Oracle stores each key value repeatedly with each stored rowid. In a bitmap index, a bitmap for each key value is used instead of a list of rowids.
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient.
Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.
Benefits for Data Warehousing Applications
Bitmap indexing benefits data warehousing applications which have large amounts of data and ad hoc queries but a low level of concurrent transactions. For such applications, bitmap indexing provides:
• Reduced response time for large classes of ad hoc queries
• A substantial reduction of space use compared to other indexing techniques
• Dramatic performance gains even on very low end hardware
• Very efficient parallel DML and loads
Bitmap Index Example
Table 10-1 shows a portion of a company's customer data.
Table 10-1 Bitmap Index Example
CUSTOMER # MARITAL_ STATUS REGION GENDER INCOME_ LEVEL
101 single east male bracket_1
102 married central female bracket_4
103 married west female bracket_2
104 divorced west male bracket_4
105 single central female bracket_2
106 married central female bracket_3
MARITAL_STATUS, REGION, GENDER, and INCOME_LEVEL are all low-cardinality columns. There are only three possible values for marital status and region, two possible values for gender, and four for income level. Therefore, it is appropriate to create bitmap indexes on these columns. A bitmap index should not be created on CUSTOMER# because this is a high-cardinality column. Instead, use a unique B-tree index on this column to provide the most efficient representation and retrieval
Table 10-2 illustrates the bitmap index for the REGION column in this example. It consists of three separate bitmaps, one for each region.


Table 10-2 Sample Bitmap
REGION='east' REGION='central' REGION='west'
1 0 0
0 1 0
0 0 1
0 0 1
0 1 0
0 1 0
Each entry or bit in the bitmap corresponds to a single row of the CUSTOMER table. The value of each bit depends upon the values of the corresponding row in the table. For instance, the bitmap REGION='east' contains a one as its first bit. This is because the region is east in the first row of the CUSTOMER table. The bitmap REGION='east' has a zero for its other bits because none of the other rows of the table contain east as their value for REGION.
Bitmap Join Indexes
A join index is an index on one table that involves columns of one or more different tables through a join.
The bitmap join index, in its simplest form, is a bitmap index on a table F based on columns from table D1,...,Dn, where Di joins with F in a star or snowflake schema as described in "Creation of a Bitmap Join Index". In the data warehousing environment, table F is usually a fact table, table Di is usually a dimension table, and the join condition is an equi-inner join between the primary key column(s) of the dimension tables and the foreign key column(s) in the fact table. For simplicity, from now on we call the table whose rowids are bitmapped the fact table, and the other tables participating in the join of bitmap join index the dimension tables.
The volume of data that must be joined can be reduced if join indexes are used as joins have already been precalculated. In addition, join indexes which contain multiple dimension tables can eliminate bitwise operations which are necessary in the star transformation with existing bitmap indexes. Finally, bitmap join indexes are much more efficient in storage than materialized join views which do not compress rowids of the fact tables.
• B-tree indexes--the default and the most common
• B-tree cluster indexes--defined specifically for cluster
• Hash cluster indexes--defined specifically for a hash cluster
• Global and local indexes--relate to partitioned tables and indexes
• Reverse key indexes--most useful for Oracle Real Application Cluster applications
• Bitmap indexes--compact; work best for columns with a small set of values
• Function-based indexes--contain the precomputed value of a function/expression
• Domain indexes--specific to an application or cartridge.


DIFFERENCE BETWEEN IF AND CASE STATEMENTS ON PERFORMANCE BASIS

The IF statement executes a sequence of statements depending on the value of a condition. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.
The CASE statement is a compact way to evaluate a single condition and choose between many alternative actions. It makes sense to use CASE when there are three or more alternatives to choose from.


Example 4-5 Extended IF-THEN Statement
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
IF grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Very Good');
ELSIF grade = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Good');
ELSIF grade = 'D' THEN
DBMS_OUTPUT. PUT_LINE('Fair');
ELSIF grade = 'F' THEN
DBMS_OUTPUT.PUT_LINE('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE('No such grade');
END IF;
ENd;
/
Using CASE Statements
Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression whose value is used to select one of several alternatives.
To compare the IF and CASE statements, consider the code in Example 4-5 that outputs descriptions of school grades. Note the five Boolean expressions. In each instance, we test whether the same variable, grade, is equal to one of five values: 'A', 'B', 'C', 'D', or 'F'. You can rewrite the code inExample 4-5 using the CASE statement, as shown in Example 4-6.
Example 4-6 Using the CASE-WHEN Statement
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
/

The CASE statement is more readable and more efficient. When possible, rewrite lengthy IF-THEN-ELSIF statements as CASE statements.
The CASE statement begins with the keyword CASE. The keyword is followed by a selector, which is the variable grade in the last example. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than BLOB, BFILE, an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.
The selector is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a WHEN-clause expression, that WHEN clause is executed. For instance, in the last example, if grade equals 'C', the program outputs 'Good'. Execution never falls through; if any WHEN clause is executed, control passes to the next statement.
The ELSE clause works similarly to the ELSE clause in an IF statement. In the last example, if the grade is not one of the choices covered by a WHEN clause, the ELSE clause is selected, and the phrase 'No such grade' is output. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:
ELSE RAISE CASE_NOT_FOUND;
There is always a default action, even when you omit the ELSE clause. If the CASE statement does not match any of the WHEN clauses and you omit the ELSE clause, PL/SQL raises the predefined exception CASE_NOT_FOUND.
The keywords END CASE terminate the CASE statement. These two keywords must be separated by a space. The CASE statement has the following form:
Like PL/SQL blocks, CASE statements can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the CASE statement. Optionally, the label name can also appear at the end of the CASE statement.
Exceptions raised during the execution of a CASE statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.
An alternative to the CASE statement is the CASE expression, where each WHEN clause is an expression. For details, see "CASE Expressions".
Searched CASE Statement
PL/SQL also provides a searched CASE statement, similar to the simple CASE statement, which has the form shown in Example 4-7.
The searched CASE statement has no selector. Also, its WHEN clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type.
MERGE STATEMENT
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.
MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.
Examples
Merging into a Table: Example The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM employees e, orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id);

SELECT * FROM bonuses;





EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1)
WHERE (S.salary <= 8000);

EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
155 170
159 180
160 175
161 170
179 620
173 610
165 680
166 640
164 720
172 730
167 620
171 740


MATERIALIZED VIEWS
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base tables or in a different database.
Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

WHEN CAN USE INDEX? AND SHOULDN’T NOT USE INDEX?

DON’T

• Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.
• Do not use standard B-tree indexes on keys or expressions with few distinct values. Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless a high concurrency OLTP application is involved where the index is modified frequently.
• Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.
• Do not index keys that appear only in WHERE clauses with functions or operators. A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path that uses the index except with function-based indexes.
• Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.
• When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of the space required to store the index. You might want to experiment by comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

WHAT IS THE DEFAULT CACHE SIZE OF SEQUENCE?

The Sequence Generator
The sequence generator provides a sequential series of numbers. The sequence generator is especially useful in multiuser environments for generating unique sequential numbers without the overhead of disk I/O or transaction locking. For example, assume two users are simultaneously inserting new employee rows into the employees table. By using a sequence to generate unique employee numbers for the employee_id column, neither user has to wait for the other to enter the next available employee number. The sequence automatically generates the correct values for each user.
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.
Sequence numbers are Oracle integers of up to 38 digits defined in the database. A sequence definition indicates general information, such as the following:
• The name of the sequence
• Whether the sequence ascends or descends
• The interval between numbers
• Whether Oracle should cache sets of generated sequence numbers in memory
Oracle stores the definitions of all sequences for a particular database as rows in a single data dictionary table in the SYSTEM tablespace. Therefore, all sequence definitions are always available, because the SYSTEM tablespace is always online.
Sequence numbers are used by SQL statements that reference the sequence. You can issue a statement to generate a new sequence number or use the current sequence number. After a statement in a user's session generates a sequence number, the particular sequence number is available only to that session. Each user that references a sequence has access to the current sequence number.
Where to Use Sequence Values
You can use CURRVAL and NEXTVAL in the following locations:
• The select list of a SELECT statement that is not contained in a subquery, materialized view, or view
• The select list of a subquery in an INSERT statement
• The VALUES clause of an INSERT statement
• The SET clause of an UPDATE statement


Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the following constructs:
• A subquery in a DELETE, SELECT, or UPDATE statement
• A query of a view or of a materialized view
• A SELECT statement with the DISTINCT operator
• A SELECT statement with a GROUP BY clause or ORDER BY clause
• A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
• The WHERE clause of a SELECT statement
• The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
• The condition of a CHECK constraint
Within a single SQL statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.
Specify the name of the sequence to be created.
If you specify none of the following clauses, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.
• To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.
• To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. Also specify NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
• To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify CYCLE. If you do not specify MINVALUE, then it defaults to NOMINVALUE, which is the value 1.
INCREMENT BY Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.
START WITH Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.
Note:
This value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.

MAXVALUE Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.
NOMAXVALUE Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
NOMINVALUE Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. This is the default.
CYCLE Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
NOCYCLE Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
CACHE Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

Note:
Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.

NOCACHE Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.
ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.
NOORDER Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
Example
Creating a Sequence: Example The following statement creates the sequence customers_seq in the sample schema oe. This sequence could be used to provide customer ID numbers when rows are added to the customers table.
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;

The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.












WHAT IS THE USE OF BIND VARIABLE?
Bind Variables
When you embed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. Oracle can reuse these SQL statement each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the appropriate places.
You do need to specify bind variables with dynamic SQL, in clauses like WHERE and VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (prefixed by a colon) and specify the corresponding PL/SQL variables with the USING clause. Using the USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle reuse the SQL statements. For example:
'DELETE FROM employees WHERE employee_id = :id' USING emp_id;
DIFFERENT TYPE OF CONSTRAINTS?

The six types of integrity constraint are described briefly here and more fully in "Semantics":
• A NOT NULL constraint prohibits a database value from being null.
• A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
• A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
• A foreign key constraint requires values in one table to match values in another table.
• A check constraint requires a value in the database to comply with a specified condition.
• A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references
NOT NULL Integrity Constraints
By default, all columns in a table allow nulls. Null means the absence of a value. A NOT NULL constraint requires a column of a table contain no null values. For example, you can define a NOT NULL constraint to require that a value be input in the last_name column for every row of the employees table.
UNIQUE Key Integrity Constraints
A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique--that is, no two rows of a table have duplicate values in a specified column or set of columns.
UNIQUE key constraint is defined on the DNAME column of the departments table to disallow rows with duplicate department names.
Unique Keys
The columns included in the definition of the UNIQUE key constraint are called the unique key. Unique key is often incorrectly used as a synonym for the terms UNIQUE key constraint or UNIQUE index. However, note that key refers only to the column or set of columns used in the definition of the integrity constraint.
If the UNIQUE key consists of more than one column, that group of columns is said to be a composite unique key. The customer table has a UNIQUE key constraint defined on the composite unique key: the area and phone columns.
Composite UNIQUE Key Constraint

This UNIQUE key constraint lets you enter an area code and telephone number any number of times, but the combination of a given area code and given telephone number cannot be duplicated in the table. This eliminates unintentional duplication of a telephone number

Combine UNIQUE Key and NOT NULL Integrity Constraints
In Figure 21-3 and Figure 21-4, UNIQUE key constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE key constraint.
Columns with both unique keys and NOT NULL integrity constraints are common. This combination forces the user to enter values in the unique key and also eliminates the possibility that any new row's data will ever conflict with an existing row's data.

PRIMARY KEY Integrity Constraints
Each table in the database can have at most one PRIMARY KEY constraint. The values in the group of one or more columns subject to this constraint constitute the unique identifier of the row. In effect, each row is named by its primary key values.
The Oracle implementation of the PRIMARY KEY integrity constraint guarantees that both of the following are true:
• No two rows of a table have duplicate values in the specified column or set of columns.
• The primary key columns do not allow nulls. That is, a value must exist for the primary key columns in each row.
Primary Keys
The columns included in the definition of a table's PRIMARY KEY integrity constraint are called the primary key. Although it is not required, every table should have a primary key so that:
• Each row in the table can be uniquely identified
• No duplicate rows exist in the table
PRIMARY KEY Constraints and Indexes
Oracle enforces all PRIMARY KEY constraints using indexes. In Figure 21-5, the primary key constraint created for the department_id column is enforced by the implicit creation of:
• A unique index on that column
• A NOT NULL constraint for that column
Oracle enforces primary key constraints using indexes, and composite primary key constraints are limited to 32 columns, which is the same limitation imposed on composite indexes. The name of the index is the same as the name of the constraint. Also, you can specify the storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint. If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one.
Referential Integrity Constraints
Different tables in a relational database can be related by common columns, and the rules that govern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved.
The following terms are associated with referential integrity constraints.
Term Definition
Foreign key The column or set of columns included in the definition of the referential integrity constraint that reference a referenced key.
Referenced key The unique key or primary key of the same or different table that is referenced by a foreign key.
Dependent or child table The table that includes the foreign key. Therefore, it is the table that is dependent on the values present in the referenced unique or primary key.
Referenced or parent table The table that is referenced by the child table's foreign key. It is this table's referenced key that determines whether specific inserts or updates are allowed in the child table.
A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.
Figure 21-6 shows a foreign key defined on the department_id column of the employees table. It guarantees that every value in this column must match a value in the primary key of the departments table (also the department_id column). Therefore, no erroneous department numbers can exist in the department_id column of the employees table.
Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.
CHECK Integrity Constraints
A CHECK integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every row of the table. If a DML statement results in the condition of the CHECK constraint evaluating to false, then the statement is rolled back.
The Check Condition
CHECK constraints enable you to enforce very specific integrity rules by specifying a check condition. The condition of a CHECK constraint has some limitations:
• It must be a Boolean expression evaluated using the values in the row being inserted or updated, and
• It cannot contain subqueries; sequences; the SQL functi
• SYSDATE, UID, USER, or USERENV; or the pseudocolumns LEVEL or ROWNUM.
In evaluating CHECK constraints that contain string literals or SQL functions with globalization support parameters as arguments (such as TO_CHAR, TO_DATE, and TO_NUMBER), Oracle uses the database globalization support settings by default. You can override the defaults by specifying globalization support parameters explicitly in such functions within the CHECK constraint definition.
Multiple CHECK Constraints
A single column can have multiple CHECK constraints that reference the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column.
If you create multiple CHECK constraints for a column, design them carefully so their purposes do not conflict. Do not assume any particular order of evaluation of the conditions. Oracle does not verify that CHECK conditions are not mutually exclusive.
The Mechanisms of Constraint Checking
To know what types of actions are permitted when constraints are present, it is useful to understand when Oracle actually performs the checking of constraints. To illustrate this, an example or two is helpful. Assume the following:
• The employees table has been defined as in Figure 21-7.
• The self-referential constraint makes the entries in the manager_id column dependent on the values of the employee_id column. For simplicity, the rest of this discussion addresses only the employee_id and manager_id columns of the employees table.
Consider the insertion of the first row into the employees table. No rows currently exist, so how can a row be entered if the value in the manager_id column cannot reference any existing value in the employee_id column? Three possibilities for doing this are:
• A null can be entered for the manager_id column of the first row, assuming that the manager_id column does not have a NOT NULL constraint defined on it. Because nulls are allowed in foreign keys, this row is inserted successfully into the table.
• The same value can be entered in both the employee_id and manager_id columns. This case reveals that Oracle performs its constraint checking after the statement has been completely executed. To allow a row to be entered with the same values in the parent key and the foreign key, Oracle must first execute the statement (that is, insert the new row) and then check to see if any row in the table has an employee_id that corresponds to the new row's manager_id.
• A multiple row INSERT statement, such as an INSERT statement with nested SELECT statement, can insert rows that reference one another. For example, the first row might have employee_id as 200 and manager_id as 300, while the second row might have employee_id as 300 and manager_id as 200.
This case also shows that constraint checking is deferred until the complete execution of the statement. All rows are inserted first, then all rows are checked for constraint violations. You can also defer the checking of constraints until the end of the transaction.
Consider the same self-referential integrity constraint in this scenario. The company has been sold. Because of this sale, all employee numbers must be updated to be the current value plus 5000 to coordinate with the new company's employee numbers. Because manager numbers are really employee numbers, these values must also increase by 5000
Constraint States
You can enable or disable integrity constraints at the table level using the CREATE TABLE or ALTER TABLE statement. You can also set constraints to VALIDATE or NOVALIDATE, in any combination with ENABLE or DISABLE, where:
• ENABLE ensures that all incoming data conforms to the constraint
• DISABLE allows incoming data, regardless of whether it conforms to the constraint
• VALIDATE ensures that existing data conforms to the constraint
• NOVALIDATE means that some existing data may not conform to the constraint
In addition:
• ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.
• ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.
In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table.
• DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and is not necessarily true.
• DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.
For a UNIQUE constraint, the DISABLE VALIDATE state enables you to load data efficiently from a nonpartitioned table into a partitioned table using the EXCHANGE PARTITION clause of the ALTER TABLE statement.
Transitions between these states are governed by the following rules:
• ENABLE implies VALIDATE, unless NOVALIDATE is specified.
• DISABLE implies NOVALIDATE, unless VALIDATE is specified.
• VALIDATE and NOVALIDATE do not have any default implications for the ENABLE and DISABLE states.
• When a unique or primary key moves from the DISABLE state to the ENABLE state, if there is no existing index, a unique index is automatically created. Similarly, when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index, the unique index is dropped.
• When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data must be checked. (This can be very slow.) However, moving from VALIDATE to NOVALIDATE simply forgets that the data was ever checked.
• Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDATE state does not block reads, writes, or other DDL statements. It can be done in parallel.
Deferred Constraint Checking
You can defer checking constraints for validity until the end of the transaction.
• A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to roll back.
• If a constraint is immediate (not deferred), then it is checked at the end of each statement. If it is violated, the statement is rolled back immediately.
If a constraint causes an action (for example, delete cascade), that action is always taken as part of the statement that caused it, whether the constraint is deferred or immediate.
OUTER JOIN VS INNER JOIN

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
Join Conditions
Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause. The join condition compares two columns, each from a different table. To execute a join, Oracle Database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.
Inner Joins
An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.
Outer Joins
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
• To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.
• To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
• To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.
You can use outer joins to fill gaps in sparse data. Such a join is called a partitioned outer join and is formed using the query_partition_clause of the join_clause syntax. Sparse data is data that does not have rows for all possible values of a dimension such as time or department. For example, tables of sales data typically do not have rows for products that had no sales on a given date. Filling data gaps is useful in situations where data sparsity complicates analytic computation or where some data might be missed if the sparse data is queried directly.
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:
• You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
• The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
• If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
• The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
• You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:
• -- The following statement is not valid:
• SELECT employee_id, manager_id
• FROM employees
• WHERE employees.manager_id(+) = employees.employee_id;
•
However, the following self join is valid:
SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id;
• The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
• A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
• A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
• A WHERE condition cannot compare any column marked with the (+) operator with a subquery.
If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.
In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C.

PRIMARY KEY VS COMPOSITE PRIMARY KEY

If the Primary key consists of more than one column, that group of columns is said to be a composite Primary key.
Oracle enforces primary key constraints using indexes, and composite primary key constraints are limited to 32 columns, which is the same limitation imposed on composite indexes. The name of the index is the same as the name of the constraint. Also, you can specify the storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint. If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one.
ROLL UP AND CUBE
ROLLUP
The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM, ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT can be used to produce other kinds of superaggregates.
For example, given three expressions (n=3) in the ROLLUP clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings.
Rows grouped on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows.
CUBE
The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.
For example, given three expressions (n=3) in the CUBE clause of the simple_grouping_clause, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows.
Using the GROUP BY Clause: Examples
To return the minimum and maximum salaries for each department in the employees table, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id;

To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary)
FROM employees
WHERE job_id = 'PU_CLERK'
GROUP BY department_id;
Using the GROUP BY CUBE Clause: Example
To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables hr.employees and hr.departments:
SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
department_name) AS department_name,
DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY CUBE (department_name, job_id);

DEPARTMENT_NAME JOB_ID Total Empl Average Sal
------------------------------ ---------- ---------- -----------
Accounting AC_ACCOUNT 1 99600
Accounting AC_MGR 1 144000
Accounting All Jobs 2 121800
Administration AD_ASST 1 52800
.
.
.
All Departments ST_MAN 5 87360
All Departments All Jobs 107 77798.1308
CUBE Extension to GROUP BY
CUBE takes a specified set of grouping columns and creates subtotals for all of their possible combinations. In terms of multidimensional analysis, CUBE generates all the subtotals that could be calculated for a data cube with the specified dimensions. If you have specified CUBE(time, region, department), the result set will include all the values that would be included in an equivalent ROLLUP statement plus additional combinations. For instance, in Example 18-1, the departmental totals across regions (279,000 and 319,000) would not be calculated by a ROLLUP(time, region, department) clause, but they would be calculated by a CUBE(time, region, department) clause. If n columns are specified for a CUBE, there will be 2 to the n combinations of subtotals returned. Example 18-3 gives an example of a three-dimension cube.
When to Use CUBE
Consider Using CUBE in any situation requiring cross-tabular reports. The data needed for cross-tabular reports can be generated with a single SELECT using CUBE. Like ROLLUP, CUBE can be helpful in generating summary tables. Note that population of summary tables is even faster if the CUBE query executes in parallel.
CUBE is typically most suitable in queries that use columns from multiple dimensions rather than columns representing different levels of a single dimension. For instance, a commonly requested cross-tabulation might need subtotals for all the combinations of month, state, and product. These are three independent dimensions, and analysis of all possible subtotal combinations is commonplace. In contrast, a cross-tabulation showing all possible combinations of year, month, and day would have several values of limited interest, because there is a natural hierarchy in the time dimension. Subtotals such as profit by day of month summed across year would be unnecessary in most analyses. Relatively few users need to ask "What were the total sales for the 16th of each month across the year?"
CUBE Syntax
CUBE appears in the GROUP BY clause in a SELECT statement. Its form is:
SELECT ... GROUP BY CUBE (grouping_column_reference_list)
Example 18-4 CUBE
SELECT channel_desc, calendar_month_desc, country_id, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales, customers, times, channels WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND sales.channel_id= channels.channel_id AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc IN ('2000-09', '2000-10') AND country_id IN ('UK', 'US') GROUP BY CUBE(channel_desc, calendar_month_desc, country_id);

CHANNEL_DESC CALENDAR CO SALES$
-------------------- -------- -- --------------
Direct Sales 2000-09 UK 1,378,126
Direct Sales 2000-09 US 2,835,557
Direct Sales 2000-09 4,213,683
Direct Sales 2000-10 UK 1,388,051
Direct Sales 2000-10 US 2,908,706
Direct Sales 2000-10 4,296,757
Direct Sales UK 2,766,177
Direct Sales US 5,744,263
Direct Sales 8,510,440
Internet 2000-09 UK 911,739
Internet 2000-09 US 1,732,240
Internet 2000-09 2,643,979
Internet 2000-10 UK 876,571
Internet 2000-10 US 1,893,753
Internet 2000-10 2,770,324
Internet UK 1,788,310
Internet US 3,625,993
Internet 5,414,303
2000-09 UK 2,289,865
2000-09 US 4,567,797
2000-09 6,857,662
2000-10 UK 2,264,622
2000-10 US 4,802,459
2000-10 7,067,081
UK 4,554,487
US 9,370,256
13,924,743

ROLLUP Extension to GROUP BY
ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.
The action of ROLLUP is straightforward: it creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.
ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies ROLLUP on grouping columns of time, region, and department (n=3), the result set will include rows at four aggregation levels.
You might want to compress your data when using ROLLUP. This is particularly useful when there are few updates to older partitions.
When to Use ROLLUP
Use the ROLLUP extension in tasks involving subtotals.
• It is very helpful for subtotaling along a hierarchical dimension such as time or geography. For instance, a query could specify a ROLLUP(y, m, day) or ROLLUP(country, state, city).
• For data warehouse administrators using summary tables, ROLLUP can simplify and speed up the maintenance of summary tables.
ROLLUP Syntax
ROLLUP appears in the GROUP BY clause in a SELECT statement. Its form is:
SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)
Example 18-2 ROLLUP
This example uses the data in the sales history store data, the same data as was used in Example 18-1. The ROLLUP is across three dimensions.
SELECT channel_desc, calendar_month_desc, country_id, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales, customers, times, channels WHERE sales.time_id=times.time_id AND
sales.cust_id=customers.cust_id AND sales.channel_id= channels.channel_id AND
channels.channel_desc IN ('Direct Sales', 'Internet') AND
times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_id IN ('UK', 'US')
GROUP BY ROLLUP(channel_desc, calendar_month_desc, country_id);

CHANNEL_DESC CALENDAR CO SALES$
-------------------- -------- -- --------------
Direct Sales 2000-09 UK 1,378,126
Direct Sales 2000-09 US 2,835,557
Direct Sales 2000-09 4,213,683
Direct Sales 2000-10 UK 1,388,051
Direct Sales 2000-10 US 2,908,706
Direct Sales 2000-10 4,296,757
Direct Sales 8,510,440
Internet 2000-09 UK 911,739
Internet 2000-09 US 1,732,240
Internet 2000-09 2,643,979
Internet 2000-10 UK 876,571
Internet 2000-10 US 1,893,753
Internet 2000-10 2,770,324
Internet 5,414,303
13,924,743

Note that results do not always add due to rounding.
This query returns the following sets of rows:
• Regular aggregation rows that would be produced by GROUP BY without using ROLLUP
• First-level subtotals aggregating across country_id for each combination of channel_desc and calendar_month
• Second-level subtotals aggregating across calendar_month_desc and country_id for each channel_desc value
• A grand total row

WHAT IS THE ADVANTAGE OF THE PROCEDURE?
Subprograms let you extend the PL/SQL language. Procedures act like new statements. Functions act like new expressions and operators.
Subprograms let you break a program down into manageable, well-defined modules. You can use top-down design and the stepwise refinement approach to problem solving.
Subprograms promote reusability. Once tested, a subprogram can be reused in any number of applications. You can call PL/SQL subprograms from many different environments, so that you do not have to reinvent the wheel each time you use a new language or API to access the database.
Subprograms promote maintainability. You can change the internals of a subprogram without changing other subprograms that call it. Subprograms play a big part in other maintainability features, such as packages and object types.
Dummy subprograms (stubs) let you defer the definition of procedures and functions until after testing the main program. You can design applications from the top down, thinking abstractly, without worrying about implementation details.
When you use PL/SQL subprograms to define an API, you can make your code even more reusable and maintainable by grouping the subprograms into a PL/SQL package.
HOW TO USE THE VARIABLE ACROSS THE PROCEDURE IN THE PACKAGES?

The spec holds public declarations, which are visible to stored procedures and other code outside the package. You must declare subprograms at the end of the spec after all other items (except pragmas that name a specific function; such pragmas must follow the function spec).
The package body contains the implementation of every cursor and subprogram declared in the package spec. Subprograms defined in a package body are accessible outside the package only if their specs also appear in the package spec. If a subprogram spec is not included in the package spec, that subprogram can only be called by other subprograms in the same package. A package body must be in the same schema as the package spec.

WHAT ARE THE HIREATCHICAL KEY QUERY? (PRIOR LIKE)
If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:
• START WITH specifies the root row(s) of the hierarchy.
• CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,
• ... PRIOR expr = expr
• or
• ... expr = PRIOR expr

If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator. For example:
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id

In addition, the CONNECT BY condition cannot contain a subquery.
PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.
The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join:
• If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing.
• If the WHERE clause does not contain a join, Oracle applies all predicates other than the CONNECT BY predicates after doing the CONNECT BY processing without affecting the other rows of the hierarchy.
Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps:
1. Oracle processes the WHERE clause either before or after the CONNECT BY clause depending on whether the WHERE clause contains any join predicates (as described in the preceding bullet list).
2. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition.
3. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.
4. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 3, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.
If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.
Oracle returns the rows in the order shown in Figure 8-1. In the diagram, children appear below their parents. For an explanation of hierarchical trees,

This graphic shows the order in which Oracle returns the rows for a query of a hierarchical tree. Beginning with the root row, Oracle returns one child row of the root, and then one child of that child row, and so forth until a leaf row (one without children) is encountered.
At this point, Oracle goes up in the hierarchy until the next row with a child row is found, and the Oracle returns that child, and so forth until the next leaf row is returned.
This process continues, with Oracle returning to the root row for the next child only when all of the "descendants" of the first child row have been returned.
To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query. The CONNECT BY condition cannot contain a subquery.
If the CONNECT BY condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.
The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:
SELECT employee_id, last_name, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101
.
The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:
SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ----------
101 Kochhar 100 1
108 Greenberg 101 2
109 Faviet 108 3
110 Chen 108 3
111 Sciarra 108 3
112 Urman 108 3
113 Popp 108 3
...

Finally, the next example adds a START WITH clause to specify a root row for the hierarchy, and an ORDER BY clause using the SIBLINGS keyword to preserve ordering within the hierarchy:
SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name;
LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL
------------------------- ----------- ---------- ----------
King 100 1
Cambrault 148 100 2
Bates 172 148 3
Bloom 169 148 3
Fox 170 148 3
Kumar 173 148 3
Ozer 168 148 3
Smith 171 148 3
De Haan 102 100 2
Hunold 103 102 3
Austin 105 103 4
Ernst 104 103 4
Lorentz 107 103 4
Pataballa 106 103 4
Errazuriz 147 100 2
Ande 166 147 3
Banda 167 147 3
...

LEVEL
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 2-1 shows the nodes of an inverted tree with their LEVEL values.
Figure 2-1 Hierarchical Tree

This graphic shows four levels of a hierarchical tree.

Level 1 contains the root/parent node of the tree. A root node is always at the top of a hierarchical tree. The root node has two "children" at Level 2. Each of these children in turn has children at Level 3. Therefore, the Level 2 nodes are parent/child nodes.

At Level 3, some of the nodes in turn have children at Level 4. These Level 3 nodes are parent/child nodes. Other Level 3 nodes have no children at Level 4. These Level 3 nodes are child/leaf nodes. A leaf node has no children at lower levels.





NO COPY HINT AND BULK COLLECTION.
This is a compiler hint (not directive), which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value (the default).
Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT parameter. When you call the subprogram, the IN parameter is passed by reference. That is, a pointer to the IN actual parameter is passed to the corresponding formal parameter. So, both parameters reference the same memory location, which holds the value of the actual parameter.
By default, the OUT and IN OUT parameters are passed by value. That is, the value of the IN OUT actual parameter is copied into the corresponding formal parameter. Then, if the subprogram exits normally, the values assigned to the OUT and IN OUT formal parameters are copied into the corresponding actual parameters.
When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference.
In the following example, you ask the compiler to pass IN OUT parameter my_staff by reference instead of by value:
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...
Remember, NOCOPY is a hint, not a directive. So, the compiler might pass my_staff by value despite your request. Usually, however, NOCOPY succeeds. So, it can benefit any PL/SQL application that passes around large data structures.
In the example below, 25000 records are loaded into a local nested table, which is passed to two local procedures that do nothing but execute NULL statements. However, a call to one procedure takes 21 seconds because of all the copying. With NOCOPY, a call to the other procedure takes much less than 1 second.
SQL> SET SERVEROUTPUT ON
SQL> GET test.sql
1 DECLARE
2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;
3 emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
4 t1 NUMBER(5);
5 t2 NUMBER(5);
6 t3 NUMBER(5);
7 PROCEDURE get_time (t OUT NUMBER) IS
8 BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;
9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
10 BEGIN NULL; END;
11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
12 BEGIN NULL; END;
13 BEGIN
14 SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788;
15 emp_tab.EXTEND(24999, 1); -- copy element 1 into 2..25000
16 get_time(t1);
17 do_nothing1(emp_tab); -- pass IN OUT parameter
18 get_time(t2);
19 do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
20 get_time(t3);
21 dbms_output.put_line('Call Duration (secs)');
22 dbms_output.put_line('--------------------');
23 dbms_output.put_line('Just IN OUT: ' || TO_CHAR(t2 - t1));
24 dbms_output.put_line('With NOCOPY: ' || TO_CHAR(t3 - t2));
25* END;
SQL> /




Call Duration (secs)
--------------------
Just IN OUT: 21
With NOCOPY: 0
The Trade-Off for Better Performance with NOCOPY
NOCOPY lets you trade well-defined exception semantics for better performance. Its use affects exception handling in the following ways:
• Because NOCOPY is a hint, not a directive, the compiler can pass NOCOPY parameters to a subprogram by value or by reference. So, if the subprogram exits with an unhandled exception, you cannot rely on the values of the NOCOPY actual parameters.
• By default, if a subprogram exits with an unhandled exception, the values assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."
• Currently, RPC protocol lets you pass parameters only by value. So, exception semantics can change silently when you partition applications. For example, if you move a local procedure with NOCOPY parameters to a remote site, those parameters will no longer be passed by reference.

BULK COLLECTION EXCEPTION?

Collection Exception Raised when...
COLLECTION_IS_NULL you try to operate on an atomically null collection.
NO_DATA_FOUND a subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT a subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT a subscript is outside the allowed range.
VALUE_ERROR a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.


BULK COLLECTION VARIABLE?
BINARY_INTEGER
You use the BINARY_INTEGER datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. Like PLS_INTEGER values, BINARY_INTEGER values require less storage than NUMBER values. However, most BINARY_INTEGER operations are slower than PLS_INTEGER operations.
BINARY_INTEGER Subtypes
A base type is the datatype from which a subtype is derived. A subtype associates a base type with a constraint and so defines a subset of values. For your convenience, PL/SQL predefines the following BINARY_INTEGER subtypes:
NATURAL
NATURALN
POSITIVE
POSITIVEN
SIGNTYPE

The subtypes NATURAL and POSITIVE let you restrict an integer variable to non-negative or positive values, respectively. NATURALN and POSITIVEN prevent the assigning of nulls to an integer variable. SIGNTYPE lets you restrict an integer variable to the values -1, 0, and 1, which is useful in programming tri-state logic.
PLS_INTEGER
You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. PLS_INTEGER values require less storage than NUMBER values. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and BINARY_INTEGER operations, which use library arithmetic. For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range.
Although PLS_INTEGER and BINARY_INTEGER have the same magnitude range, they are not fully compatible. When a PLS_INTEGER calculation overflows, an exception is raised. However, when a BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to a NUMBER variable.
Because of this small semantic difference, you might want to continue using BINARY_INTEGER in old applications for compatibility. In new applications, always use PLS_INTEGER for better performance.
PL/SQL offers these collection types:
• Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
• Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

TYPE OF TABLES
Tables are the basic unit of data storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name (such as employees) and set of columns. You give each column a column name (such as employee_id, last_name, and job_id), a datatype (such as VARCHAR2, DATE, or NUMBER), and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
Partitioned Tables
Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Indexes can be partitioned in similar fashion. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.
Nested Tables
You can create a table with a column whose datatype is another table. That is, tables can be nested within other tables as values in a column. The Oracle server stores nested table data out of line from the rows of the parent table, using a store table that is associated with the nested table column. The parent row contains a unique set identifier value associated with a nested table instance.
Temporary Tables
In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.
The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

External Tables
You can access data in external sources as if it were in a table in the database. You can connect to the database and create metadata for the external table, using DDL. The DDL for an external table consists of two parts: one part that describes the Oracle column types, another part (the access parameters) which describes the mapping of the external data to the Oracle data columns.
MUTATING ERROR AND HOW TO SOLVE THIS ERROR?

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.
When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
Consider the following trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||
' employees.');
END;

If the following SQL statement is entered:
DELETE FROM Emp_tab WHERE Empno = 7499;
An error is returned because the table is mutating when the row is deleted:
ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it

If you delete the line "FOR EACH ROW" from the trigger, it becomes a statement trigger which is not subject to this restriction, and the trigger.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers—an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
Declarative integrity constraints are checked at various times with respect to row triggers.
Because declarative referential integrity constraints are not supported between tables on different nodes of a distributed database, the mutating table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining an Oracle Net path back to the database that contains the link.

Restrictions on Mutating Tables Relaxed
The mutating error, discussed earlier in this section, still prevents the trigger from reading or modifying the table that the parent statement is modifying. However, starting in Oracle Database release 8.1, a delete against the parent table causes before/after statement triggers to be fired once. That way, you can create triggers (just not row triggers) to read and modify the parent and child tables.
This allows most foreign key constraint actions to be implemented through their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily. For example, this is an implementation of update cascade:
create table p (p1 number constraint ppk primary key);
create table f (f1 number constraint ffk references p);
create trigger pt after update on p for each row begin
update f set f1 = :new.p1 where f1 = :old.p1;
end;
/

This implementation requires care for multirow updates. For example, if a table p has three rows with the values (1), (2), (3), and table f also has three rows with the values (1), (2), (3), then the following statement updates p correctly but causes problems when the trigger updates f:
update p set p1 = p1+1;

The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost.
To avoid this problem, you must forbid multirow updates to p that change the primary key and reuse existing primary key values. It could also be solved by tracking which foreign key values have already been updated, then modifying the trigger so that no row is updated twice.
That is the only problem with this technique for foreign key updates. The trigger cannot miss rows that have been changed but not committed by another transaction, because the foreign key constraint guarantees that no matching foreign key rows are locked before the after-row trigger is called.

SYSTEM TRIGGERS
Tracking System Events Using Triggers
Fine-Grained Access Control Using Triggers: Example System triggers can be used to set application context. Application context is a relatively new feature that enhances your ability to implement fine-grained access control. Application context is a secure session cache, and it can be used to store session-specific attributes.
In the example that follows, procedure set_ctx sets the application context based on the user profile. The trigger setexpensectx ensures that the context is set for every user.
Responding to System Events through Triggers
System event publication lets applications subscribe to database events, just like they subscribe to messages from other applications. The system events publication framework includes the following features:

• Infrastructure for publish/subscribe, by making the database an active publisher of events.
• Integration of data cartridges in the server. The system events publication can be used to notify cartridges of state changes in the server.
• Integration of fine-grained access control in the server.
By creating a trigger, you can specify a procedure that runs when an event occurs. DML events are supported on tables, and system events are supported on DATABASE and SCHEMA. You can turn notification on and off by enabling and disabling the trigger using the ALTER TRIGGER statement.
This feature is integrated with the Advanced Queueing engine. Publish/subscribe applications use the DBMS_AQ.ENQUEUE() procedure, and other applications such as cartridges use callouts.
• Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)
PRAGMA
"AUTONOMOUS_TRANSACTION Pragma"

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.
PRAGMA
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
Usage Notes
You can apply this pragma to:
• Top-level (not nested) anonymous PL/SQL blocks
• Local, standalone, and packaged functions and procedures
• Methods of a SQL object type
• Database triggers
You cannot apply this pragma to an entire package or an entire an object type. Instead, you can apply the pragma to each packaged subprogram or object method.
You can code the pragma anywhere in the declarative section. For readability, code the pragma at the top of the section.
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.
Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.
Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.
In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.
If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.
"EXCEPTION_INIT Pragma"

The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.

DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
NULL; -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN deadlock_detected THEN
NULL; -- handle the error
END;
/

"SERIALLY_REUSABLE Pragma"
The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. An example could be an OCI call to the database or a stored procedure call through a database link. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
PRAGMA
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.
Usage Notes
This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.
You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.
The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.
Example 13-5 Creating a Serially Reusable Package
CREATE PACKAGE pkg1 IS
PRAGMA SERIALLY_REUSABLE;
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
/
CREATE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num);
END;
END pkg1;
DBMS_JOB

The DBMS_JOB package schedules and manages jobs in the job queue.
Working with Real Application Clusters
DBMS_JOB supports multi-instance execution of jobs. By default jobs can be executed on any instance, but only one single instance will execute the job. In addition, you can force instance binding by binding the job to a particular instance. You implement instance binding by specifying an instance number to the instance affinity parameter. Note, however, that in Oracle Database 10g Release 1 (10.1) instance binding is not recommended. Service affinity is preferred.
DBMS_JOB.SUBMIT
To submit a job to the job queue, use the following syntax:
DBMS_JOB.SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);

Use the parameters instance and force to control job and instance affinity. The default value of instance is 0 (zero) to indicate that any instance can execute the job. To run the job on a certain instance, specify the instance value. Oracle displays error ORA-23319 if the instance value is a negative number or NULL.
The force parameter defaults to false. If force is TRUE, any positive integer is acceptable as the job instance. If force is FALSE, the specified instance must be running, or Oracle displays error number ORA-23428.
DBMS_JOB.INSTANCE
To assign a particular instance to execute a job, use the following syntax:
DBMS_JOB.INSTANCE( JOB IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE);

The FORCE parameter in this example defaults to FALSE. If the instance value is 0 (zero), job affinity is altered and any available instance can execute the job despite the value of force. If the INSTANCE value is positive and the FORCE parameter is FALSE, job affinity is altered only if the specified instance is running, or Oracle displays error ORA-23428.
If the force parameter is TRUE, any positive integer is acceptable as the job instance and the job affinity is altered. Oracle displays error ORA-23319 if the instance value is negative or NULL.
DBMS_JOB.CHANGE
To alter user-definable parameters associated with a job, use the following syntax:
DBMS_JOB.CHANGE( JOB IN BINARY_INTEGER,
what IN VARCHAR2 DEFAULT NULL,
next_date IN DATE DEFAULT NULL,
interval IN VARCHAR2 DEFAULT NULL,
instance IN BINARY_INTEGER DEFAULT NULL,
force IN BOOLEAN DEFAULT FALSE );

Two parameters, instance and force, appear in this example. The default value of instance is null indicating that job affinity will not change.
The default value of force is FALSE. Oracle displays error ORA-23428 if the specified instance is not running and error ORA-23319 if the instance number is negative.
DBMS_JOB.RUN
The force parameter for DBMS_JOB.RUN defaults to FALSE. If force is TRUE, instance affinity is irrelevant for running jobs in the foreground process. If force is FALSE, the job can run in the foreground only in the specified instance. Oracle displays error ORA-23428 if force is FALSE and the connected instance is the incorrect instance.
DBMS_JOB.RUN(
job IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE);


LEVEL OF CONSTRAINTS ( COLUMN LEVEL AND TABLE LEVEL)

A.Column level means what are the constraint we can put on column.

Like
Create table a (b number check b>0);
Create table a (b number Not Null);



B.Column level means what are the constraint we can put on column.

Like
Create table a (b number Primary Key);
Create table a (b number Unique);








































DBMS_DDL

SAS






LEVEL OF CONSTRAINTS ( COLUMN LEVEL AND TABLE LEVEL)


BIT MAP INDEX AND HASH INDEX

SAS


SET SERVEROUTPUT ON (BACKROUND PROCESS DETAILS)?

WHAT IS ENTITY?

HOW TO PRINT THE BIND VARIABLE IN SQL PROMPT?

WHAT IS THE DIFFERENT BETWEEN SQL & SQL * PLUS?

WHAT IS THE DIFFERENT BETWEEN BIND VARIABLE VS LEXCIAL VARIABLE?

ENTITY RESULT SET?

HOW TO SELECT THE DATA FROM THE NESTLE TABLE?

WHAT IS MEAN BY NESTED TABLE?

IMPLICIT CURSOR VS EXPLICIT CURSOR AND EXAMPLES?
















































**********************************************************************************************************************
**********************************************************************************************************************

TUNING HINTS
62.WHAT IS TRIGGER?
63.THE TABLE IS AVAILABLE COLUMNS QTY,PRICE AND AMOUNT? QTY AND PRICE IS COMING FROM FORM
WE INSERT THE AMOUNT COLUMN (QTY * AMOUNT) BY THE WAY OF TRIGGER.
WHAT IS THE ADVANTAGE OF THE PROCEDURE?
WHAT ARE THE HIRE KEY QUERY? (PRIOR LIKE)
HOW TO USE THE VARIABLE ACROSS THE PROCEDURE IN THE PACKAGES?
SET SERVEROUTPUT ON (BACKROUND PROCESS DETAILS)?
ENTITY RELATIONSHIP DIAGRAM?
WHAT IS ENTITY?
HOW TO PRINT THE BIND VARIABLE IN SQL PROMPT?
WHAT IS THE DIFFERENT BETWEEN SQL & SQL * PLUS?
WHAT IS THE DIFFERENT BETWEEN BIND VARIABLE VS LEXCIAL VARIABLE?
ENTITY RESULT SET?
HOW TO SELECT THE DATA FROM THE NESTLE TABLE?
75 . WHAT IS MEAN BY NESTED TABLE?
IMPLICIT CURSOR VS EXPLICIT CURSOR AND EXAMPLES?


CAN WE USE SINGLE ROW FUNCTIONS IN WHERE CLASS? CAN WE USE NESTED SINGLE ROW FUNCTIONS IN WHERE CLASS?

WHAT WILL BE THE OUTPUT OF ROUND(SYSDATE,'YEAR') ASSUME SYSDATE IS 01-JUL-2006?

WHAT ARE ALL THE SDLC STEPS?

WHAT IS QUERY REWRITING

ERROR PROPAGATION

TABLE DESIGN ,INSERT , UPDATE STRATEGIES

SDLC

WHY WILL U USE PL/SQL LIBRARY WITH IN OBJECT LIBRARY ?

HOW SYSTEM.MESSAGE_LEVEL WILL HELP IN THE FORMS SCRIPT ?

WHY WILL U CHOOSE OBJECT GROUP - SHOULD BE UNIQE FEATURE.( SHOULD NOT MINGLE OBJECT LIBARY )

WHY WILL U CHOOSE OBJECT LIBRARY - SHOULD BE UNIQE FEATURE.( SHOULD NOT MINGLE OBJECT GROUP )

WHY WE WILL GO FOR TRIGGER OVERRIDE?

WHY CAN'T USE GLOBAL TEMPORARY TABLE INSTEAD OF PL/SQL TABLE?
WHAT IS THE COMPLEX SITUATION U HAVE FACED IN YOUR CARREAR?

PRE QUERY VS PRE SELECT VS PRE FETCH.

RUN REPORT OBJECT. ???
FOR ALL , BULK UPDATE WHERE WILL U USE , SHOULD BE UNIQUE MERITS.

HOW TO TUNE SQL QUERIES

HOW TO TUNE PL/SQL BLOCK

WHAT ARE THE WAYS TO IDENTIFY THE PERFORMANCE ISSUES

COST AND CARDINALITY IN EXPLAIN PLAN

HOW WILL YOU IDENTIFY THAT THE QUERY OR A PLSQL BLOCK HAVING PERFORMANCE PROBLEM

SQL TRACE , TKPROF AND PROFILER

WHT WILL YOU DO TO GET BETTER PERFORMANCE
TYPES OF TABLES
NEW FEATURES IN 9I,10G ETC.
WHAT IS THE USE OF THE SAVE EXCEPTIONS?

LEVEL OF CONSTRAINTS ( COLUMN LEVEL AND TABLE LEVEL)