Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 14 July 2012

Resumable Space Management features

Resumable space management can be used to manage the space failures of long running transactions as it has significant impacts on a session. For Example:
·         Running out of Tablespaces space – It include errors in which Oracle cannot allocate another extent because of a lack of available space. For Example: ORA-1650 (Unable to extend rollback segment), ORA-1653 (Unable to extend table), and ORA-1654 (Unable to extend index). Adding space to the given tablespace generally solves this issue. If the error involves a temporary tablespaces then other user sessions frees space for suspended session by releasing the temporary segments.
·         Maximum number of extents reached - It includes errors such as ORA-1628 (Max number of extents reached for rollback segment), ORA-1631 and ORA-1654 (Max number of extents reached in Table or Index). In this case, the object has a MAX_EXTENTS value that has been reached. Increasing the MAX_EXTENTS setting for the object will solve these issues. 
·         Attempt to exceed a tablespace quota - This includes error ORA-1536 (space quota exceeded for tablespace). To solve this issue, increase the users' quota for the specified tablespace.
If resumable space management has been enabled for the session and when one of the above conditions is reached, the resumable statement will be suspended (until the time out period). When the statement is suspended, an error will be raised in the alert log. During this period, if the condition causing the statement to be suspended is corrected, the statement will resume execution automatically otherwise an error will be raised and the statement is automatically rolled back.
Select * from USER_RESUMABLE; Select * from USER_RESUMABLE;
By using above two views, suspended operations can be monitored and fixed.
Note: It can also be managed by DBMS_RESUMABLE built-in package
By default the time out period is 2 (7200 seconds) hours here in above example it is overridden by 3 (10800 seconds) hours. You can also specify the name with each statement will make it easier to identify.
SQL> select user_id, session_id, error_msg from dba_resumable;‎
‎-------- ---------‎ ‎---------------------------------------------‎
‎14          9       ‎ ORA-30036: unable to extend segment by 128 in undo tablespace ‎‎'SECOND_UNDO'‎
In the above example, session 9 has a suspended view. From the ERROR_MSG column we can see that it has stalled waiting for undo space in the UNDO tablespace named SECOND_UNDO. There are a couple of options the DBA would have to resume this statement:
- Add space to the UNDO tablespace, or enable AUTOEXTEND. 
- Wait for the undo segments to be released by other transactions so they could be used.
Once Oracle has resolved the problem, the suspended session will automatically be restarted.
You can also query suspended operation with V$SESSION_WAIT.
SQL> select sid, event, seconds_in_wait from v$session_wait WHERE sid = 9;

SID      EVENT                                              SECONDS_IN_WAIT
----     --------------------------------------------        ----------------
 9       statement suspended, wait error to be cleared                   625
The V$SYSTEM_EVENT and V$SESSION_EVENT views provide wait information on suspension events.
SQL> select event, total_waits, time_waited from v$system_event where event like '%suspend%';
EVENT                                          TOTAL_WAITS TIME_WAITED
---------------------------------------------- ----------- -----------
statement suspended, wait error to be cleared          178       36208
SQL> select sid, event, total_waits, time_waited from v$session_event where event like '%suspend%';
SID EVENT                                         TOTAL_WAITS TIME_WAITED
---------- --------------------------------------------- ----------- -----------
9   statement suspended, wait error to be cleared         167       39504
When used with dictionary-managed tablespaces, there are certain limitations on resumable space management.
– If the creation of a table or index fails with the DDL including an explicit maxextents clause and statement fails then the object creation will not be resumable. To correct this problem, set maxextents to UNLIMITED while creating the object. This does not apply to DML operation and locally managed tablespaces.
– If a rollback-segment space-allocation fails, and the associated rollback segment is in a dictionary-managed tablespace, the operation will fail. This does not apply if you are using UNDO tablespaces, or if the tablespace that the rollback segments belong to is locally managed.
Note: Remote operations statements, such as DML statements using database links, do not support resumable space management. If the parallel execution server process receives any nonrecoverable error, the entire statement will fail and any suspended processes will be aborted.
There are many operation that can use Resumable Space Management such as SQL*loader, Import/export operation, Create table as select, Alter Table Movie Partition, Alter table rebuild partition, Create Materialized view etc.
New parameters have been added to the IMPORT and SQL*Loader utilities to ease the use of resumable space management in Oracle9i such as: resumable_timeout, resumable_name, resumable
If an IMP session is suspended, the IMP process will stop until the suspension time-out passes. If the space failure is fixed during the suspension, the IMP process will continue to process the work until it is either finished or suspended again. By default, the resumable space features are not enabled with the IMP facility.


Post a Comment