【脚本】Resumable Space Allocation Example

Resumable Space Allocation Example

Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors [ID 136941.1]

1. The user running the transaction requires the RESUMABLE system privilege:

SQL> conn system/oracle
Connected.

SQL> grant resumable to scott;
Grant succeeded.

 

resumable权限也被包含在其它role里面，比如dba，当一个用户拥有dba role时，就自动拥有了resumable privilege.

 

2. Set the session so that the following transactions might be resumed in case of interruption due to space allocation:

 SQL> alter session enable resumable;
 Session altered.

 

This can be set automatically through anAFTER LOGON trigger.
    --也可以通过触发器来自动设置

  SQL> create or replace trigger logon_set_resumable
    2  after logon
    3  on scott.schema
    4  begin
    5  	execute immediate 'alter session enable resumable timeout 1200';
    6  end;
    7  /
 
  Trigger created.

 
 3. While inserting newrows into TEST_RESUMABLE table, the user session hangs,but the transaction doesnot roll back:

The DBA can retrieve（得到；取到） the reason why the session of user SCOTT hangs in DBA_RESUMABLE view:
 
3.1 Displaying the DBA_RESUMABLE view:

   SQL> select user_id,SESSION_ID, STATUS, START_TIME, SUSPEND_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;

      USER_ID SESSION_ID STATUS    START_TIME           SUSPEND_TIME
   ---------- ---------- --------- -------------------- --------------------
   SQL_TEXT
   -------------------------------------------------------------------------
   ERROR_NUMBER
   ------------
   ERROR_MSG
   -------------------------------------------------------------------------
           54          9 SUSPENDED 03/14/01 10:49:25    03/14/01 11:14:17
   insert into test_resumable select * from test_resumable
           1631
   ORA-01631: max # extents (5) reached in table SCOTT.TEST_RESUMABLE

 
3.2 In alert.log file:

     Wed Mar 14 11:14:17 2001
     statement in resumable session 'User SCOTT(54), Session 9, Instance 1'was suspended due to ORA-01631: max # extents (5) reached in table SCOTT.TEST_RESUMABLE

 
3.3 The statement may issue the following error when the timeout set for the session has expired:
 

      SQL> insert into test_resumable values (1);

      insert into test_resumable values (1)
               *
      ERROR at line 1:
      ORA-30032: the suspended (resumable) statement has timed out
      ORA-01536: space quota exceeded for tablespace 'EXAMPLE'

 

 

3.4 The DBA now knows why the session hangs, and needs to find which action to take to alleviate the ora-01536 error:

   SQL>  connect system/manager
   Connected.

   SQL> alter table scott.test_resumable storage (max extents 8);
   Table altered.

 
In alert.log file:

      Wed Mar 14 11:24:02 2001
      statement in resumable session 'User SCOTT(54), Session 9, Instance 1' 
      was resumed and no more errors in DBA_RESUMABLE view:

   SQL>select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;
 

      USER_ID SESSION_ID STATUS    START_TIME           RESUME_TIME  
   ---------- ---------- --------- -------------------- --------------------
   SQL_TEXT
   -------------------------------------------------------------------------
   ERROR_NUMBER
   ------------
   ERROR_MSG
   -------------------------------------------------------------------------
           54          9 NORMAL    03/14/01 10:49:25    03/14/01 11:24:02
   insert into test_resumable select * from test_resumable
              0
 

  

   While the status is NORMAL or the error_number is 0, the resumable statements keep on working correctly unless the timeout is expired.
   This also means that there are sessions set in resumable state.
   As soon as an error_number <> 0 appears, then a resumable session has encountered a space allocation issue.
 
Note:
   The DBA can cancel the resumable transaction by aborting the session by the procedure DBMS_RESUMABLE.ABORT(sid#). An ORA-1013 "user requested cancel of current operation" is returned to the user.

 

3.5 If the session does not need to be in resumable state, the session can disable the resumable state:

   SQL> alter session disable resumable;
   Session altered.
 

   SQL> select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;
 
   no rows selected

 
3.6 Other space errors that suspend transactions
-------------------------------------------------------------------
***
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was suspended due to ORA-01536: space quota exceeded for tablespace 'EXAMPLE'
 
***
statement in resumable session 'User SCOTT(54), Session 8, Instance 1' was suspended due to ORA-01562: failed to extend rollback segment number 11
 
***
statement in resumable session 'User SCOTT(54), Session 8, Instance 1' was suspended due to ORA-01628: max # extents (2) reached for rollback segment RS01 FULL status of rollback segment 11 set
 
***
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was suspended due to ORA-01631: max # extents (2) reached in table SCOTT.TEST_RESUMABLE 
 
***
statement in resumable session 'User SYSTEM(5), Session 8, Instance 1' was suspended due to ORA-01652: unable to extend temp segment by 32 in tablespace TEMP_TS 
 
***
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was suspended due to ORA-01653: unable to extend table SCOTT.TEST_RESUMABLE by 256 in tablespace USERS 
 
***
statement in resumable session 'User SCOTT(34), Session 8, Instance 1' was suspended due to ORA-01654: unable to extend index SCOTT.SYS_IOT_TOP_27956 by 8 in tablespace PERM_DICT_2K
 
***
statement in resumable session 'User SYSTEM(5), Session 11, Instance 1' was suspended due to ORA-01658: unable to create INITIAL extent for segment in tablespace LMT_1
 
***
statement in resumable session 'User SYSTEM(5), Session 11, Instance 1' was suspended due to ORA-01659: unable to allocate MINEXTENTS beyond 42 in tablespace LMT_1
 
3.7 Other messages in alert.log:
***
Wed Mar 14 10:43:52 2001
statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was aborted