2 SQL Processing for Application Developers

DDL Statement Processing

The execution of DDL statements differs from the execution of DML statements and queries, because the success of a DDL statement requires write access to the data dictionary. For these statements, parsing (Stage 2) actually includes parsing, data dictionary lookup, and execution.

Transaction Control Processing

In general, only application designers using the programming interfaces to Oracle Database are concerned with the types of actions that are grouped together as one transaction. Transactions must be defined so that work is accomplished in logical units and data is kept consistent. A transaction consists of all of the necessary parts for one logical unit of work, no more and no less.

• Data in all referenced tables should be in a consistent state before the transaction begins and after it ends.

• Transactions should consist of only the SQL statements that make one consistent change to the data.

For example, a transfer of funds between two accounts (the transaction or logical unit of work) should include the debit to one account (one SQL statement) and the credit to another account (one SQL statement). Both actions should either fail or succeed together as a unit of work; the credit should not be committed without the debit. Other unrelated actions, such as a new deposit to one account, should not be included in the transfer of funds transaction.

Other Processing Types

Transaction management, session management, and system management SQL statements are processed using the parse and run stages. To rerun them, simply perform another execute.

Deciding How to Group Operations in Transactions

In general, deciding how to group operations in transactions is the concern of application designers who use the programming interfaces to Oracle Database. When deciding how to group transactions:

• Define transactions such that work is accomplished in logical units and data remains consistent.

• Ensure that data in all referenced tables is in a consistent state before the transaction begins and after it ends.

• Ensure that each transaction consists only of the SQL statements or PL/SQL blocks that comprise one consistent change to the data.

For example, suppose that you write a Web application that enables users to transfer funds between accounts. The transaction must include the debit to one account, which is executed by one SQL statement, and the credit to another account, which is executed by a second SQL statement. Both statements must fail or succeed together as a unit of work; the credit must not be committed without the debit. Other unrelated actions, such as a new deposit to one account, must not be included in the same transaction.

Improving Transaction Performance

As an application developer, you must consider whether you can improve performance. Consider the following performance enhancements when designing and writing your application:

• Use the SET TRANSACTION statement with the USE ROLLBACK SEGMENT clause to explicitly assign a transaction to a rollback segment. This technique can eliminate the need to allocate additional extents dynamically, which can reduce system performance. This clause is valid only if you use rollback segments for undo. If you use automatic undo management, then Oracle Database ignores this clause.

• Establish standards for writing SQL statements so that you can take advantage of shared SQL areas. Oracle Database recognizes identical SQL statements and enables them to share memory areas. This reduces memory usage on the database server and increases system throughput.

• Collect statistics that can be used by Oracle Database to implement a cost-based approach to SQL statement optimization. You can supply additional "hints" to the optimizer as needed.

  For the collection of most statistics, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. For more information about this package, see Oracle Database PL/SQL Packages and Types Reference.

  For statistics collection not related to the cost-based optimizer (such as collecting information about freelist blocks), use the SQL statement ANALYZE. For more information about this statement, see Oracle Database SQL Language Reference.

• Invoke the DBMS_APPLICATION_INFO.SET_ACTION procedure before beginning a transaction to register and name a transaction for later use when measuring performance across an application. Specify which type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources.

• Increase user productivity and query efficiency by including user-written PL/SQL functions in SQL expressions as described in Invoking Stored PL/SQL Functions from SQL Statements.

• Create explicit cursors when writing a PL/SQL application.

• Reduce frequency of parsing and improve performance in precompiler programs by increasing the number of cursors with MAX_OPEN_CURSORS.

• Use the SET TRANSACTION statement with the ISOLATION LEVEL set to SERIALIZABLE to get ANSI/ISO serializable transactions.

Committing Transactions

To commit a transaction, use the COMMIT statement. The following two statements are equivalent and commit the current transaction:

COMMIT WORK;
COMMIT;

The COMMIT statements lets you include the COMMENT parameter along with a comment that provides information about the transaction being committed. This option is useful for including information about the origin of the transaction when you commit distributed transactions:

COMMIT COMMENT 'Dallas/Accts_pay/Trans_type 10B';

Managing Commit Redo Action

When a transaction updates the database, it generates a redo entry corresponding to this update. Oracle Database buffers this redo in memory until the completion of the transaction. When the transaction commits, the log writer process (LGWR) writes redo for the commit, along with the accumulated redo of all changes in the transaction, to disk. By default, Oracle Database writes the redo to disk before the call returns to the client. This action introduces a latency in the commit because the application must wait for the redo to be persisted on disk.

Suppose that you are writing an application that requires very high transaction throughput. If you are willing to trade commit durability for lower commit latency, then you can change the default COMMIT options so that the application need not wait for the database to write data to the online redo logs.

Oracle Database enables you to change the handling of commit redo depending on the needs of your application. You can change the commit action in the following locations:

• COMMIT_WRITE initialization parameter at the system or session level

• COMMIT statement

The options in the COMMIT statement override the current settings in the initialization parameter. Table 2-1 describes redo persistence options that you can set in either location.

The following example shows how to set the commit action to BATCH and NOWAIT in the initialization parameter file:

COMMIT_WRITE = BATCH, NOWAIT

You can change the commit action at the system level by executing ALTER SYSTEM as in the following example:

ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT

After the initialization parameter is set, a COMMIT statement with no options conforms to the options specified in the parameter. Alternatively, you can override the current initialization parameter setting by specifying options directly on the COMMIT statement as in the following example:

COMMIT WRITE BATCH NOWAIT

In either case, your application specifies that log writer does not have to write the redo for the commit immediately to the online redo logs and need not wait for confirmation that the redo was written to disk.

If your application uses OCI, then you can modify redo action by setting the following flags in the OCITransCommit function within your application:

• OCI_TRANS_WRITEBATCH

• OCI_TRANS_WRITENOWAIT

• OCI_TRANS_WRITEIMMED

• OCI_TRANS_WRITEWAIT

The specification of the NOWAIT and BATCH options has a small window of vulnerability in which Oracle Database can roll back a transaction that your application view as committed. Your application must be able to tolerate the following scenarios:

• The database host fails, which causes the database to lose redo that was buffered but not yet written to the online redo logs.

• A file I/O problem prevents log writer from writing buffered redo to disk. If the redo logs are not multiplexed, then the commit is lost.

Rolling Back Transactions

To roll back an entire transaction, or to roll back part of a transaction to a savepoint, use the ROLLBACK statement. For example, either of the following statements rolls back the entire current transaction:

ROLLBACK WORK;
ROLLBACK;

The WORK option of the ROLLBACK statement has no function.

To roll back to a savepoint defined in the current transaction, use the TO option of the ROLLBACK statement. For example, either of the following statements rolls back the current transaction to the savepoint named POINT1:

SAVEPOINT Point1;
...
ROLLBACK TO SAVEPOINT Point1;
ROLLBACK TO Point1;

Defining Transaction Savepoints

To define a savepoint in a transaction, use the SAVEPOINT statement. The following statement creates the savepoint named ADD_EMP1 in the current transaction:

SAVEPOINT Add_emp1;

If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After creating a savepoint, you can roll back to the savepoint.

There is no limit on the number of active savepoints for each session. An active savepoint is one that was specified since the last commit or rollback.

Table 2-2 shows a series of SQL statements that illustrates the use of COMMIT, SAVEPOINT, and ROLLBACK statements within a transaction.

How Many Cursors Can a Session Have?

There is no absolute limit to the total number of cursors one session can have open at one time, subject to two constraints:

• Each cursor requires virtual memory, so a session's total number of cursors is limited by the memory available to that process.

• A systemwide limit of cursors for each session is set by the initialization parameter named OPEN_CURSORS found in the parameter file (such as INIT.ORA).

  See Also:

  Oracle Database Reference for more information about OPEN_CURSORS

Explicitly creating cursors for precompiler programs has advantages in tuning those applications. For example, increasing the number of cursors can reduce the frequency of parsing and improve performance. If you know how many cursors might be required at a given time, you can open that many cursors simultaneously.

Using a Cursor to Reexecute a Statement

After each stage of execution, the cursor retains enough information about the SQL statement to reexecute the statement without starting over, as long as no other SQL statement was associated with that cursor. The statement can be reexecuted without including the parse stage.

By opening several cursors, the parsed representation of several SQL statements can be saved. Repeated execution of the same SQL statements can thus begin at the describe, define, bind, or execute step, saving the repeated cost of opening cursors and parsing.

To understand the performance characteristics of a cursor, a DBA can retrieve the text of the query represented by the cursor using the V$SQL dynamic performance view. Because the results of EXPLAIN PLAN on the original query might differ from the way the query is actually processed, a DBA can get more precise information by examining the following dynamic performance views:

Closing a Cursor

Closing a cursor means that the information currently in the associated private area is lost and its memory is deallocated. Once a cursor is opened, it is not closed until one of the following events occurs:

• The user program terminates its connection to the server.

• If the user program is an OCI program or precompiler application, then it explicitly closes any open cursor during the execution of that program. (However, when this program terminates, any cursors remaining open are implicitly closed.)

Canceling a Cursor

Canceling a cursor frees resources from the current fetch.The information currently in the associated private area is lost but the cursor remains open, parsed, and associated with its bind variables.

Privileges Required

You can automatically acquire any type of table lock on tables in your schema. To acquire a table lock on a table in another schema, you must have the LOCK ANY TABLE system privilege or any object privilege (for example, SELECT or UPDATE) for the table.

Choosing a Locking Strategy

A transaction explicitly acquires the specified table locks when a LOCK TABLE statement is executed. A LOCK TABLE statement manually overrides default locking. When a LOCK TABLE statement is issued on a view, the underlying base tables are locked. The following statement acquires exclusive table locks for the emp_tab and dept_tab tables on behalf of the containing transaction:

LOCK TABLE emp_tab, dept_tab
    IN EXCLUSIVE MODE NOWAIT;

You can specify several tables or views to lock in the same mode; however, only a single lock mode can be specified for each LOCK TABLE statement.

In the LOCK TABLE statement, you can also indicate how long you want to wait for the table lock:

• If you do not want to wait, specify either NOWAIT or WAIT 0.

  You acquire the table lock only if it is immediately available; otherwise, an error notifies you that the lock is not available at this time.

• If you want to wait up to n seconds to acquire the table lock, specify WAIT n, where n is greater than 0 and less than or equal to 100000.

  If the table lock is still unavailable after n seconds, an error notifies you that the lock is not available at this time.

• If you want to wait indefinitely to acquire the lock, specify neither NOWAIT nor WAIT.

  The database waits indefinitely until the table is available, locks it, and returns control to you. When the database is executing DDL statements concurrently with DML statements, a timeout or deadlock can sometimes result. The database detects such timeouts and deadlocks and returns an error.

For the syntax of the LOCK TABLE statement, see Oracle Database SQL Language Reference.

Topics:

• When to Lock with ROW SHARE MODE and ROW EXCLUSIVE MODE

• When to Lock with SHARE MODE

• When to Lock with SHARE ROW EXCLUSIVE MODE

• When to Lock with EXCLUSIVE MODE

SQL> -- Create and populate table:
SQL> 
SQL> DROP TABLE budget_tab;
DROP TABLE budget_tab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> CREATE TABLE budget_tab (
  2    sal     NUMBER(8,2),
  3    deptno  NUMBER(4));
 
Table created.
 
SQL> INSERT INTO budget_tab
  2    SELECT salary, department_id
  3      FROM employees;
 
107 rows created.
 
SQL> -- Lock departments and update employees and budget_tab:
SQL> 
SQL> LOCK TABLE departments IN SHARE MODE;
 
Table(s) Locked.
 
SQL> UPDATE employees
  2    SET salary = salary * 1.1
  3      WHERE department_id IN
  4        (SELECT department_id FROM departments WHERE location_id = 1700);
 
18 rows updated.
 
SQL> UPDATE budget_tab
  2    SET sal = sal * 1.1
  3      WHERE deptno IN
  4        (SELECT department_id FROM departments WHERE location_id = 1700);
 
18 rows updated.
 
SQL> -- COMMIT releases lock
SQL> COMMIT;
SQL> 

Letting Oracle Database Control Table Locking

Letting Oracle Database control table locking means your application needs less programming logic, but also has less control, than if you manage the table locks yourself.

Issuing the statement SET TRANSACTION ISOLATION LEVEL SERIALIZABLE or ALTER SESSION ISOLATION LEVEL SERIALIZABLE preserves ANSI serializability without changing the underlying locking protocol. This technique gives concurrent access to the table while providing ANSI serializability. Getting table locks greatly reduces concurrency.

Change the settings for these parameters only when an instance is shut down. If multiple instances are accessing a single database, then all instances must use the same setting for these parameters.

Explicitly Acquiring Row Locks

You can override default locking with a SELECT statement that includes the FOR UPDATE clause. This statement acquires exclusive row locks for selected rows (as an UPDATE statement does), in anticipation of updating the selected rows in a subsequent statement.

You can use a SELECT FOR UPDATE statement to lock a row without actually changing it. For example, several triggers in Oracle Database PL/SQL Language Reference show how to implement referential integrity. In the EMP_DEPT_CHECK trigger, the row that contains the referenced parent key value is locked to guarantee that it remains for the duration of the transaction; if the parent key is updated or deleted, referential integrity is violated.

SELECT FOR UPDATE statements are often used by interactive programs that enable a user to modify fields of one or more specific rows (which might take some time); row locks are acquired so that only a single interactive program user is updating the rows at any given time.

If a SELECT FOR UPDATE statement is used when defining a cursor, the rows in the return set are locked when the cursor is opened (before the first fetch) rather than being locked as they are fetched from the cursor. Locks are only released when the transaction that opened the cursor is committed or rolled back, not when the cursor is closed.

Each row in the return set of a SELECT FOR UPDATE statement is locked individually; the SELECT FOR UPDATE statement waits until the other transaction releases the conflicting row lock. If a SELECT FOR UPDATE statement locks many rows in a table, and if the table experiences a lot of update activity, it might be faster to acquire an EXCLUSIVE table lock instead.

By default, the transaction waits until the requested row lock is acquired. If you are not willing to wait to acquire the row lock, use either the NOWAIT clause of the LOCK TABLE statement (see Choosing a Locking Strategy) or the SKIP LOCKED clause of the SELECT FOR UPDATE statement.

If you can lock some of the requested rows, but not all of them, the SKIP LOCKED option skips the rows that you cannot lock and locks the rows that you can lock.

When to Use User Locks

User locks can help to:

• Provide exclusive access to a device, such as a terminal

• Provide application-level enforcement of read locks

• Detect when a lock is released and cleanup after the application

• Synchronize applications and enforce sequential processing

The following Pro*COBOL precompiler example shows how locks can be used to ensure that there are no conflicts when multiple people must access a single device.

****************************************************************** 
* Print Check                                                    * 
* Any cashier may issue a refund to a customer returning goods.  * 
* Refunds under $50 are given in cash, more than $50 by check.   * 
* This code prints the check. One printer is opened by all       * 
* the cashiers to avoid the overhead of opening and closing it   * 
* for every check, meaning that lines of output from multiple    * 
* cashiers can become interleaved if you do not ensure exclusive * 
* access to the printer. The DBMS_LOCK package is used to        * 
* ensure exclusive access.                                       * 
****************************************************************** 
CHECK-PRINT 
*    Get the lock "handle" for the printer lock. 
   MOVE "CHECKPRINT" TO LOCKNAME-ARR. 
   MOVE 10 TO LOCKNAME-LEN. 
   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.ALLOCATE_UNIQUE ( :LOCKNAME, :LOCKHANDLE ); 
      END; END-EXEC. 
*   Lock the printer in exclusive mode (default mode).
   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.REQUEST ( :LOCKHANDLE ); 
      END; END-EXEC. 
*   You now have exclusive use of the printer, print the check. 
  ... 
*   Unlock the printer so other people can use it 
EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.RELEASE ( :LOCKHANDLE ); 
      END; END-EXEC.

Viewing and Monitoring Locks

Table 2-5 describes the Oracle Database facilities that display locking information for ongoing transactions within an instance.

How Serializable Transactions Interact

Figure 2-1 shows how a serializable transaction (Transaction B) interacts with another transaction (A, which can be either SERIALIZABLE or READ COMMITTED).

When a serializable transaction fails with ORA-08177, the application can take any of several actions:

• Commit the work executed to that point

• Execute additional, different, statements, perhaps after rolling back to a prior savepoint in the transaction

• Roll back the entire transaction and try it again

Oracle Database stores control information in each data block to manage access by concurrent transactions. To use the SERIALIZABLE isolation level, you must use the INITRANS clause of the CREATE TABLE or ALTER TABLE statement to set aside storage for this control information. To use serializable mode, INITRANS must be set to at least 3.

Figure 2-1 Time Line for Two Transactions

Description of "Figure 2-1 Time Line for Two Transactions"

Setting the Isolation Level of a Serializable Transaction

You can change the isolation level of a transaction using the ISOLATION LEVEL clause of the SET TRANSACTION statement, which must be the first statement issued in a transaction.

Use the ALTER SESSION statement to set the transaction isolation level on a session-wide basis.

Oracle Database stores control information in each data block to manage access by concurrent transactions. Therefore, if you set the transaction isolation level to SERIALIZABLE, then you must use the ALTER TABLE statement to set INITRANS to at least 3. This parameter causes Oracle Database to allocate sufficient storage in each block to record the history of recent transactions that accessed the block. Use higher values for tables that will undergo many transactions updating the same blocks.

Referential Integrity and Serializable Transactions

Because Oracle Database does not use read locks, even in SERIALIZABLE transactions, data read by one transaction can be overwritten by another. Transactions that perform database consistency checks at the application level must not assume that the data they read will not change during the execution of the transaction (even though such changes are not visible to the transaction). Database inconsistencies can result unless such application-level consistency checks are coded carefully, even when using SERIALIZABLE transactions.

Figure 2-2 shows two different transactions that perform application-level checks to maintain the referential integrity parent/child relationship between two tables. One transaction checks that a row with a specific primary key value exists in the parent table before inserting corresponding child rows. The other transaction checks to see that no corresponding detail rows exist before deleting a parent row. In this case, both transactions assume (but do not ensure) that data they read will not change before the transaction completes.

Figure 2-2 Referential Integrity Check

Description of "Figure 2-2 Referential Integrity Check"

The read issued by transaction A does not prevent transaction B from deleting the parent row, and transaction B's query for child rows does not prevent transaction A from inserting child rows. This scenario leaves a child row in the database with no corresponding parent row. This result occurs even if both A and B are SERIALIZABLE transactions, because neither transaction prevents the other from making changes in the data it reads to check consistency.

As this example shows, sometimes you must take steps to ensure that the data read by one transaction is not concurrently written by another. This requires a greater degree of transaction isolation than defined by SQL92 SERIALIZABLE mode.

Fortunately, it is straightforward in Oracle Database to prevent the anomaly described:

• Transaction A can use SELECT FOR UPDATE to query and lock the parent row and thereby prevent transaction B from deleting the row.

• Transaction B can prevent Transaction A from gaining access to the parent row by reversing the order of its processing steps. Transaction B first deletes the parent row, and then rolls back if its subsequent query detects the presence of corresponding rows in the child table.

Referential integrity can also be enforced in Oracle Database using database triggers, instead of a separate query as in Transaction A. For example, an INSERT into the child table can fire a BEFORE INSERT row-level trigger to check for the corresponding parent row. The trigger queries the parent table using SELECT FOR UPDATE, ensuring that parent row (if it exists) remains in the database for the duration of the transaction inserting the child row. If the corresponding parent row does not exist, the trigger rejects the insert of the child row.

SQL statements issued by a database trigger execute in the context of the SQL statement that caused the trigger to fire. All SQL statements executed within a trigger see the database in the same state as the triggering statement. Thus, in a READ COMMITTED transaction, the SQL statements in a trigger see the database as of the beginning of the triggering statement execution, and in a transaction executing in SERIALIZABLE mode, the SQL statements see the database as of the beginning of the transaction. In either case, the use of SELECT FOR UPDATE by the trigger correctly enforces referential integrity.

READ COMMITTED and SERIALIZABLE Isolation

Oracle Database gives you a choice of two transaction isolation levels with different characteristics. Both the READ COMMITTED and SERIALIZABLE isolation levels provide a high degree of consistency and concurrency. Both levels reduce contention, and are designed for deploying real-world applications. The rest of this topic compares the two isolation modes and provides information helpful in choosing between them.

Topics:

• Transaction Set Consistency

• Comparison of READ COMMITTED and SERIALIZABLE Transactions

• Choosing an Isolation Level for Transactions

Application Tips for Transactions

When a transaction runs in serializable mode, any attempt to change data that was changed by another transaction since the beginning of the serializable transaction causes ORA-08177.

When you get this error, roll back the current transaction and execute it again. The transaction gets a new transaction snapshot, and the operation is likely to succeed.

To minimize the performance overhead of rolling back transactions and executing them again, try to put DML statements that might conflict with other concurrent transactions near the beginning of your transaction.

Examples of Autonomous Transactions

• Ordering a Product

• Withdrawing Money from a Bank Account

As these examples illustrate, there are four possible outcomes when you use autonomous and main transactions (see Table 2-7). There is no dependency between the outcome of an autonomous transaction and that of a main transaction.

Ordering a Product

In the example illustrated by Figure 2-5, a customer orders a product. The customer's information (such as name, address, phone) is committed to a customer information table—even though the sale does not go through.

Figure 2-5 Example: A Buy Order

Description of "Figure 2-5 Example: A Buy Order"

Withdrawing Money from a Bank Account

In this example, a customer tries to withdraw money from a bank account. In the process, a main transaction invokes one of two autonomous transaction scopes (AT Scope 1 or AT Scope 2).

The possible scenarios for this transaction are:

• Scenario 1: Sufficient Funds

• Scenario 2: Insufficient Funds with Overdraft Protection

• Scenario 3: Insufficient Funds Without Overdraft Protection

Scenario 1: Sufficient Funds

There are sufficient funds to cover the withdrawal, so the bank releases the funds (see Figure 2-6).

Figure 2-6 Bank Withdrawal—Sufficient Funds

Description of "Figure 2-6 Bank Withdrawal—Sufficient Funds"

Scenario 2: Insufficient Funds with Overdraft Protection

There are insufficient funds to cover the withdrawal, but the customer has overdraft protection, so the bank releases the funds (see Figure 2-7).

Figure 2-7 Bank Withdrawal—Insufficient Funds with Overdraft Protection

Description of "Figure 2-7 Bank Withdrawal—Insufficient Funds with Overdraft Protection"

Scenario 3: Insufficient Funds Without Overdraft Protection

There are insufficient funds to cover the withdrawal and the customer does not have overdraft protection, so the bank withholds the requested funds (see Figure 2-8).

Figure 2-8 Bank Withdrawal—Insufficient Funds Without Overdraft Protection

Description of "Figure 2-8 Bank Withdrawal—Insufficient Funds Without Overdraft Protection"

Defining Autonomous Transactions

To define autonomous transactions, use PRAGMA AUTONOMOUS_TRANSACTION, which instructs the PL/SQL compiler to mark the routine as autonomous.

In Example 2-3, the function balance is autonomous.

SQL> -- Create table that package will use:
SQL> 
SQL> DROP TABLE accounts;
DROP TABLE accounts
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> CREATE TABLE accounts (account INTEGER, balance REAL);
 
Table created.
 
SQL> -- Create package:
SQL> 
SQL> CREATE OR REPLACE PACKAGE banking AS
  2    FUNCTION balance (acct_id INTEGER) RETURN REAL;
  3    -- Additional functions and packages
  4  END banking;
  5  /
 
Package created.
 
SQL> CREATE OR REPLACE PACKAGE BODY banking AS
  2    FUNCTION balance (acct_id INTEGER) RETURN REAL IS
  3      PRAGMA AUTONOMOUS_TRANSACTION;
  4      my_bal  REAL;
  5    BEGIN
  6      SELECT balance INTO my_bal FROM accounts
  7        WHERE account=acct_id;
  8      RETURN my_bal;
  9    END;
 10    -- Additional functions and packages
 11  END banking;
 12  /
 
Package body created.
 
SQL> 

What Operations Can Be Resumed After an Error Condition?

Queries, DML operations, and certain DDL operations can all be resumed if they encounter an out-of-space error. The capability applies if the operation is performed directly by a SQL statement, or if it is performed within a stored subprogram, anonymous PL/SQL block, SQL*Loader, or an OCI call such as OCIStmtExecute.

Operations can be resumed after these kinds of error conditions:

• Out of space errors, such as ORA-01653.

• Space limit errors, such as ORA-01628.

• Space quota errors, such as ORA-01536.

Certain storage errors cannot be handled using this technique. In dictionary-managed tablespaces, you cannot resume an operation if you run into the limit for rollback segments, or the maximum number of extents while creating an index or a table. Use locally managed tablespaces and automatic undo management in combination with this feature.

Handling Suspended Storage Allocation

When a statement is suspended, your application does not receive the usual error code. Therefore, it must do any logging or notification by coding a trigger to detect the AFTER SUSPEND event and invoke functions in the DBMS_RESUMABLE package to get information about the problem.

Within the body of the trigger, you can perform any notifications, such as sending e-mail to alert an operator to the space problem.

Alternatively, the DBA can periodically check for suspended statements using the static data dictionary view DBA_RESUMABLE and the dynamic performance view V$_SESSION_WAIT.

When the space condition is corrected (usually by the DBA), the suspended statement automatically resumes execution. If not corrected before the timeout period expires, the statement raises a SERVERERROR exception.

To reduce the chance of out-of-space errors within the trigger itself, declare it as an autonomous transaction, so that it uses a rollback segment in the SYSTEM tablespace. If the trigger encounters a deadlock condition because of locks held by the suspended statement, the trigger terminates and your application receives the original error condition, as if the statement was never suspended. If the trigger encounters an out-of-space condition, both the trigger and the suspended statement are rolled back. You can prevent the rollback through an exception handler in the trigger, and wait for the statement to be resumed.

The trigger in Example 2-4 handles storage errors within the database. For some kinds of errors, it terminates the statement and alerts the DBA that this has happened through an e-mail. For other errors, which might be temporary, it specifies that the statement waits for eight hours before resuming, with the expectation that the storage problem will be fixed by then. To execute this example, you must be logged in as SYSDBA.

SQL> -- Create table used by trigger body
SQL> 
SQL> DROP TABLE rbs_error;
DROP TABLE rbs_error
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> CREATE TABLE rbs_error (
  2    SQL_TEXT VARCHAR2(64),
  3    ERROR_MSG VARCHAR2(64),
  4    SUSPEND_TIME VARCHAR2(64));
 
Table created.
 
SQL> -- Resumable Storage Allocation
SQL> 
SQL> CREATE OR REPLACE TRIGGER suspend_example
  2    AFTER SUSPEND
  3    ON DATABASE
  4  DECLARE
  5    cur_sid           NUMBER;
  6    cur_inst          NUMBER;
  7    err_type          VARCHAR2(64);
  8    object_owner      VARCHAR2(64);
  9    object_type       VARCHAR2(64);
 10    table_space_name  VARCHAR2(64);
 11    object_name       VARCHAR2(64);
 12    sub_object_name   VARCHAR2(64);
 13    msg_body          VARCHAR2(64);
 14    ret_value         BOOLEAN;
 15    error_txt         VARCHAR2(64);
 16    mail_conn         UTL_SMTP.CONNECTION;
 17  BEGIN
 18    SELECT DISTINCT(SID) INTO cur_sid FROM V$MYSTAT;
 19    cur_inst := USERENV('instance');
 20    ret_value := DBMS_RESUMABLE.SPACE_ERROR_INFO
 21                 (err_type,
 22                  object_owner,
 23                  object_type,
 24                  table_space_name,
 25                  object_name,
 26                  sub_object_name);
 27    IF object_type = 'ROLLBACK SEGMENT' THEN
 28      INSERT INTO rbs_error
 29        (SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
 30           FROM DBA_RESUMABLE
 31             WHERE SESSION_ID = cur_sid
 32               AND INSTANCE_ID = cur_inst);
 33      SELECT ERROR_MSG INTO error_txt
 34        FROM DBA_RESUMABLE
 35          WHERE SESSION_ID = cur_sid
 36            AND INSTANCE_ID = cur_inst;
 37      msg_body :=
 38      'Space error occurred: Space limit reached for rollback segment '
 39      || object_name || ' on ' || to_char(SYSDATE, 'Month dd, YYYY, HH:MIam')
 40      || '. Error message was: ' || error_txt;
 41      mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);
 42      UTL_SMTP.HELO(mail_conn, 'localhost');
 43      UTL_SMTP.MAIL(mail_conn, 'sender@localhost');
 44      UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');
 45      UTL_SMTP.DATA(mail_conn, msg_body);
 46      UTL_SMTP.QUIT(mail_conn);
 47      DBMS_RESUMABLE.ABORT(cur_sid);
 48    ELSE
 49      DBMS_RESUMABLE.SET_TIMEOUT(3600*8);
 50    END IF;
 51    COMMIT;
 52  END;
 53  /
 
Trigger created.
 
SQL>