柔中带刚，刚中带柔，淫荡中富含柔和，刚猛中荡漾风骚，无坚不摧，无孔不入！
全部博文（1669）
分类： Oracle
2012-01-11 09:24:46
一.Resumable Space Allocation 说明
从9i开始，Oracle提供了一种避免因为space Error而导致事务异常的操作: resumable spaceallocation. 在Oracle 10g的OCP 考试中有考resumable session 的这个知识点。
官网对Resumable的解释：
Advantage is forDBAs who are running yearly reports and do not know how much extra disk spaceis required for TEMP, UNDO or DATA tablespaces in order to complete the job.This functionality does not break the job, but raises an alert for the DBA tofix the issue. The job automatically resumes once the DBA has fixed the issue.
--当我们执行一个事务操作，如果使用了resumable space allocation，那么如果遇到space空间不够的情况，事务不会中断，而是生成一条alert log 发送给DBA，当DBA 解决这个问题之后，事务自动恢复运行。
在Oracle 10g中对resumable session功能做了增强，这些新特性在MOS的文档上有说明：
10g NEW FEATUREon RESUMABLE TIMEOUT [ID 240991.1]
10g中增强的内容有：
1）增加了一个resumable_timeout的参数
该参数可以在system和session level级均可以修改.对RAC db,每个instance可以单独设置. 而9i中只能在session一级中设置。
A resumableoperation is suspended whenever it encounters some space issue. (See DatabaseAdministrator's Guide for information about enabling resumable space allocation, what conditions areresumable, and what statements can be made resumable. For example,DEADLOCK or ORA-00600 error are not resumable situations).
Once theoperation is suspended, an alert message is sent to the DBA. Once the causethat caused the failure is fixed, the suspended statement automatically resumesits execution. Every"resumable" operation has a time-out period associated. The defaultvalue of the time-out period is 2 hours (unless the user issues an altersession enable resumable). A suspended operation is automatically aborted ifthe error condition is not fixed within the "time-out".
--设置resumable_timeout 之后，在指定的timeout 时间内会自动恢复，如果超过这个时间没有解决问题，事务操作还是会被中断。
2）对分布式事务的支持
Distributed Transactions Behavior
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In 9i, users arenot allowed to start a distributed transaction in a resumable enabled session. And if a session has a distributedtransaction, users are not allowed to enable resumable.
Theserestrictions are removed in 10g. However, in a distributed transaction, if usersenable/disable resumable or change resumable_timeout, only the local instancesare affected. In a distributed transaction, sessions on remote instances aresuspended if resumable has been enabled in the remote instance.
3）增加了一个监控的视图
在10g中，我们也可以通过DBA_OUTSTANDING_ALERTS来监控resumablesession.
二.官网说明
Oracle 11gR2的官方的说明链接如下：
ManagingResumable Space Allocation
Oracle Database provides a means for suspending, and later resuming, the execution of largedatabase operations in the event of space allocation failures. Therefore, youcan take corrective action instead of the Oracle Database server returning anerror to the user. After the error condition is corrected, the suspendedoperation automatically resumes. This feature iscalled resumable space allocation. The statements that are affectedare called resumable statements.
2.1 ResumableSpace Allocation Overview
This sectionprovides an overview of resumable space allocation. It describes how resumablespace allocation works, and specifically defines qualifying statements anderror conditions.
2.1.1 HowResumable Space Allocation Works
--Resumable Space Allocation 是如何工作的
The following isan overview of how resumable space allocation works. Details are contained inlater sections.
--Resumable space allocation 的工作机制如下：
1. A statementexecutes in a resumable mode only if its session has been enabled for resumablespace allocation by one of the following actions:
--启动session 的resumable spaceallocation，相关的2个actions：
（1）The RESUMABLE_TIMEOUT initializationparameter is set to a nonzero value.
（2） The ALTER SESSION ENABLE RESUMABLE statement is issued.
2.A resumablestatement is suspended when one of the following conditions occur (theseconditions result in corresponding errors being signalled for non-resumablestatements):
--在启动resumablesession 之后，当发生以下情况，resumable的语句就会被suspend。
（1）Out of space condition
（2）Maximum extents reached condition
（3）Space quota exceeded condition.
3.When theexecution of a resumable statement is suspended, there are mechanisms toperform user supplied operations, log errors, and to query the status of the statementexecution. When a resumable statement is suspended the following actions aretaken:
--当resumable 的statement 被suspend，那么相关的机制被会执行，如生成log 信息。 具体的log 有如下3类：
（1）The error is reported in the alertlog.
（2）The system issues the ResumableSession Suspended alert.
（3）If the user registered a trigger onthe AFTER SUSPEND system event, the user trigger is executed. A usersupplied PL/SQL procedure can access the error message data using the DBMS_RESUMABLEpackageand the DBA_ or USER_RESUMABLE view.
4.Suspending astatement automatically results in suspending the transaction. Thus all transactionalresources are held through a statement suspend and resume.
--suspend 的事物自动转到suspend 事务中，并且事务所有相关的资源都会通过suspend 和 resume 被hold。
5.When the errorcondition is resolved (for example, as a result of user intervention or perhapssort space released by other queries), the suspended statement automaticallyresumes execution and the Resumable Session Suspended alert is cleared.
--当error 被解决之后，被suspend的statement会自动的执行，resumable session的suspended 的alert 也会自动清除 。
6.A suspendedstatement can be forced to throw the exception using the DBMS_RESUMABLE.ABORT() procedure.This procedure can be called by a DBA, or by the user who issued the statement.
--suspend 的statement 也可以使用DBMS_RESUMABLE.ABORT()过程来强制中断。
7.A suspensiontime out interval is associated with resumable statements. A resumablestatement that is suspended for the timeout interval (the default is two hours)wakes up and returns the exception to the user.
--suspension time的设置于resumable的时间相关，该参数的默认值是2个小时，即如果在2个小时内，resumable 的session 没有被wakeup，那么就会中断并反馈异常。
8.A resumablestatement can be suspended and resumed multiple times during execution.
--resumable statement 在执行时可以被多次suspend 和 resume。
2.1.2 WhatOperations are Resumable?
The following operations are resumable:
--如下操作可以被resumable：
1.Queries
SELECT statementsthat run out of temporary space (for sort areas) are candidates for resumableexecution. When using OCI, the calls OCIStmtExecute() and OCIStmtFetch() arecandidates.
2.DML
INSERT, UPDATE,and DELETE statements are candidates. The interface used to executethem does not matter; it can be OCI, SQLJ, PL/SQL, or another interface. Also, INSERTINTO...SELECT from external tables can be resumable.
3.Import/Export
As forSQL*Loader, a command line parameter controls whether statements are resumableafter recoverable errors.
4.DDL
The following statements are candidates forresumable execution:
（1）CREATE TABLE ... AS SELECT
（2）CREATE INDEX
（3）ALTER INDEX ... REBUILD
（4）ALTER TABLE ... MOVE PARTITION
（5）ALTER TABLE ... SPLIT PARTITION
（6）ALTER INDEX ... REBUILD PARTITION
（7）ALTER INDEX ... SPLIT PARTITION
（8）CREATE MATERIALIZED VIEW
（9）CREATE MATERIALIZED VIEW LOG
2.1.3 WhatErrors are Correctable?
There are three classes of correctableerrors:
--触发resumable的三种情况：
1.Out of space condition
The operationcannot acquire any more extents for a table/index/temporary segment/undosegment/cluster/LOB/table partition/index partition in a tablespace. Forexample, the following errors fall in this category:
unableto extend table ... in tablespace ...
unableto extend index ... in tablespace ...
2.Maximum extents reachedcondition
The number ofextents in a table/index/temporary segment/undo segment/cluster/LOB/tablepartition/index partition equals the maximum extents defined on the object. Forexample, the following errors fall in this category:
max #extents ... reached in table ...
max #extents ... reached in index ...
3.Space quota exceededcondition
The user ha***ceeded his assigned space quota in the tablespace. Specifically, this isnoted by the following error:
spacequote exceeded for tablespace string
2.1.4 ResumableSpace Allocation and Distributed Operations
In a distributedenvironment, if a user enables or disables resumable space allocation, or ifyou, as a DBA, alter the RESUMABLE_TIMEOUT initialization parameter,only the local instance is affected. In a distributed transaction, sessions orremote instances are suspended only if RESUMABLE has been enabled inthe remote instance.
--在distributed environment中，resumable的配置只对本地的实例有效，如果要在远程实例上生效就需要在远程实力上配置。
2.1.5 ParallelExecution and Resumable Space Allocation
In parallelexecution, if one of the parallel execution server processes encounters acorrectable error, that server process suspends its execution. Other parallelexecution server processes will continue executing their respective tasks,until either they encounter an error or are blocked (directly or indirectly) bythe suspended server process. When the correctable error is resolved, thesuspended process resumes execution and the parallel operation continue***ecution. If the suspended operation is terminated, the parallel operationaborts, throwing the error to the user.
--对于并行执行，如果其中的一个parallel execution 进程遇到了conrrectable error，那么其对应的serverprocess 就会被suspend，其他的parallel 进程继续执行，直到他们遇到error 或者被suspend进程block。 当correctable error 被解决后，suspend 进程会被重新执行。 如果suspend操作被中断，那么对应的并行操作也会被中断，然后抛出错误给用户。
Different parallel execution server processes may encounter one or more correctableerrors. This may result in firing an AFTER SUSPEND trigger multipletimes, in parallel. Also, if a parallel execution server process encounters anon-correctable error while another parallel execution server process issuspended, the suspended statement is immediately aborted.
不同的并行进程可能遇到一个或者多个correctableerrors。 这些错误由一个叫AFTER SUSPEND 的触发器来发出。当如果某个并行的进程遇到一个non-correctableerror，而其他的并行进程是suspend，那么suspend 的事务就会被立即中断。
For parallelexecution, every parallel execution coordinator and server process has its ownentry in the DBA_ or USER_RESUMABLE view.
--对于并行执行，可以查看DBA_RESUMABLE 和 USER_RESUMABLE 视图。
2.2 Enablingand Disabling Resumable Space Allocation
Resumable spaceallocation is only possible when statements are executed within a session thathas resumable mode enabled. There are two means of enabling and disablingresumable space allocation. You can control it at the system level with the RESUMABLE_TIMEOUT initializationparameter, or users can enable it at the session level using clauses of the ALTERSESSION statement.
--可以在系统级别或者session 级别设置resumable spaceallocation。
Note:
Becausesuspended statements can hold up some system resources, users must be grantedthe RESUMABLE system privilege before they are allowed to enableresumable space allocation and execute resumable statements.
--因为suspend 需要hold 一些系统资源，所有执行该操作的用户必须要先获取RESUMABLE的权限。
2.2.1 Settingthe RESUMABLE_TIMEOUT Initialization Parameter
--系统级别设置
You can enableresumable space allocation system wide and specify a timeout interval bysetting the RESUMABLE_TIMEOUT initialization parameter.
--在系统级别设置需要在初始化参数里添加RESUMABLE_TIMEOUT 参数。
For example, thefollowing setting of the RESUMABLE_TIMEOUTparameter in the initializationparameter file causes all sessions to initially be enabled for resumable spaceallocation and sets the timeout period to 1 hour:
RESUMABLE_TIMEOUT = 3600
--这里设置timeout为1小时。
If thisparameter is set to 0, then resumable space allocation is disabled initiallyfor all sessions. This is the default.
--如果参数设置为0，则禁用resumable。 默认情况就是0，即默认是禁用该功能的。
You can use the ALTERSYSTEM SET statement to change the value of this parameter at the systemlevel.
--也可以使用alter system set 语句来进行设置。
For example, thefollowing statement will disable resumable space allocation for all sessions:
ALTER SYSTEM SETRESUMABLE_TIMEOUT=0;
Within asession, a user can issue the ALTER SESSION SET statement to set the RESUMABLE_TIMEOUT initializationparameter and enable resumable space allocation, change a timeout value, or todisable resumable mode.
2.2.2 Using ALTER SESSION to Enable and Disable Resumable SpaceAllocation
--在session 级别设置
A user can enable resumable mode for asession, using the following SQL statement:
ALTER SESSIONENABLE RESUMABLE;
To disable resumable mode, a user issuesthe following statement:
ALTER SESSIONDISABLE RESUMABLE;
The default fora new session is resumable mode disabled, unless the RESUMABLE_TIMEOUT initializationparameter is set to a nonzero value.
The user canalso specify a timeout interval, and can provide a name used to identify aresumable statement. These are discussed separately in following sections.
（1）Specifying aTimeout Interval
A timeoutperiod, after which a suspended statement will error if no intervention hastaken place, can be specified when resumable mode is enabled. The followingstatement specifies that resumable transactions will time out and error after3600 seconds:
ALTER SESSIONENABLE RESUMABLE TIMEOUT 3600;
The value of TIMEOUT remainsin effect until it is changed by another ALTER SESSION ENABLE RESUMABLE statement,it is changed by another means, or the session ends. The default timeoutinterval when using the ENABLE RESUMABLE TIMEOUT clause to enableresumable mode is 7200 seconds.
（2）Naming Resumable Statements
Resumablestatements can be identified by name. The following statement assigns a name toresumable statements:
ALTER SESSIONENABLE RESUMABLE TIMEOUT 3600 NAME 'insert into table';
The NAME valueremains in effect until it is changed by another ALTER SESSION ENABLERESUMABLE statement, or the session ends. The default value for NAME is'User username(userid), Session sessionid,Instance instanceid'.
The name of thestatement is used to identify the resumable statement in the DBA_RESUMABLE and USER_RESUMABLE views.
2.2.3 Usinga LOGON Trigger to Set Default Resumable Mode
Another methodof setting default resumable mode, other than setting the RESUMABLE_TIMEOUT initializationparameter, is that you can register a database level LOGON trigger toalter a user's session to enable resumable and set a timeout interval.
Note:
If there aremultiple triggers registered that change default mode and timeout for resumablestatements, the result will be unspecified because Oracle Database does notguarantee the order of trigger invocation.
2.3 DetectingSuspended Statements
When a resumablestatement is suspended, the error is not raised to the client. In order forcorrective action to be taken, Oracle Database provides alternative methods fornotifying users of the error and for providing information about thecircumstances.
--当一个resumable 语句被suspend，这个error不会自动返回给用户，Oracle 提供一些方法来将这些error 信息通知用户。
2.3.1 NotifyingUsers: The AFTER SUSPEND System Event and Trigger
When a resumablestatement encounter a correctable error, the system internally generates the AFTER SUSPEND system event. Users can registertriggers for this event at both the database and schema level. If a userregisters a trigger to handle this system event, the trigger is executed aftera SQL statement has been suspended.
--当遇到correctable error 时，系统内部使用AFTER SUSPEND 触发器。 用户可以在DB 或者schema 级别注册这个触发器。 如果用户注册触发器来处理这个事件，那么触发器会在SQL 语句suspend 后执行。
SQL statement***ecuted within a AFTER SUSPEND trigger arealways non-resumable and are always autonomous. Transactions started within thetrigger use the SYSTEM rollback segment. These conditions are imposedto overcome deadlocks and reduce the chance of the trigger experiencing thesame error condition as the statement.
Users can usethe USER_RESUMABLE or DBA_RESUMABLE views, or the DBMS_RESUMABLE.SPACE_ERROR_INFO function, within triggers to getinformation about the resumable statements.
--在这个触发器中可以使用 USER_RESUMABLE ， DBA_RESUMABLE 视图或者DBMS_RESUMABLE.SPACE_ERROR_INFO函数来获取更多的resumable 语句信息。
Triggers canalso call the DBMS_RESUMABLE package to terminate suspendedstatements and modify resumable timeout values. In the following example, thedefault system timeout is changed by creating asystem wide AFTER SUSPEND trigger that calls DBMS_RESUMABLE toset the timeout to 3 hours:
--在触发器中也可以调用 DBMS_RESUMABLE 包来中断suspended语句和修改resumable timeout 时间。
CREATE OR REPLACE TRIGGERresumable_default_timeout
AFTER SUSPEND
ON DATABASE
BEGIN
DBMS_RESUMABLE.SET_TIMEOUT(10800);
END;
/
2.3.2 UsingViews to Obtain Information About Suspended Statements
The followingviews can be queried to obtain information about the status of resumablestatements:
|
View |
Description |
|
DBA_RESUMABLE USER_RESUMABLE |
These views contain rows for all currently executing or suspended resumable statements. They can be used by a DBA, AFTER SUSPEND trigger, or another session to monitor the progress of, or obtain specific information about, resumable statements. |
|
V$SESSION_WAIT |
When a statement is suspended the session invoking the statement is put into a wait state. A row is inserted into this view for the session with the EVENTcolumn containing "statement suspended, wait error to be cleared". |
2.3.3 Usingthe DBMS_RESUMABLE Package
The DBMS_RESUMABLE packagehelps control resumable space allocation. The following procedures can beinvoked:
|
Procedure |
Description |
|
ABORT(sessionID) |
This procedure aborts a suspended resumable statement. The parameter sessionID is the session ID in which the statement is executing. For parallel DML/DDL, sessionID is any session ID which participates in the parallel DML/DDL. Oracle Database guarantees that the ABORT operation always succeeds. It may be called either inside or outside of the AFTER SUSPEND trigger. The caller of ABORT must be the owner of the session with sessionID, have ALTER SYSTEM privilege, or have DBA privileges. |
|
GET_SESSION_TIMEOUT(sessionID) |
This function returns the current timeout value of resumable space allocation for the session with sessionID. This returned timeout is in seconds. If the session does not exist, this function returns -1. |
|
SET_SESSION_TIMEOUT(sessionID, timeout) |
This procedure sets the timeout interval of resumable space allocation for the session with sessionID. The parameter timeout is in seconds. The newtimeout setting will applies to the session immediately. If the session does not exist, no action is taken. |
|
GET_TIMEOUT() |
This function returns the current timeout value of resumable space allocation for the current session. The returned value is in seconds. |
|
SET_TIMEOUT(timeout) |
This procedure sets a timeout value for resumable space allocation for the current session. The parameter timeout is in seconds. The new timeout setting applies to the session immediately. |
2.4 Operation-SuspendedAlert
When a resumablesession is suspended, an operation-suspended alert is issued on the object thatneeds allocation of resource for the operation to complete. Once the resourceis allocated and the operation completes, the operation-suspended alert iscleared. See for more information on system-generatedalerts.
2.5 Resumable Space AllocationExample: Registering an AFTER SUSPEND Trigger
AFTER SUSPEND 触发器示例
In the followingexample, a system wide AFTER SUSPEND trigger is created andregistered as user SYS at the database level. Whenever a resumablestatement is suspended in any session, this trigger can have either of twoeffects:
（1）If an undosegment has reached its space limit, then a message is sent to the DBA and thestatement is aborted.
（2）If any otherrecoverable error has occurred, the timeout interval is reset to 8 hours.
Here are the statementsfor this example:
CREATE OR REPLACE TRIGGER resumable_default
AFTER SUSPEND
ON DATABASE
DECLARE
/*declare transaction in this trigger is autonomous */
/*this is not required because transactions within a trigger
are always autonomous */
PRAGMA AUTONOMOUS_TRANSACTION;
cur_sid NUMBER;
cur_inst NUMBER;
errno NUMBER;
err_type VARCHAR2;
object_owner VARCHAR2;
object_type VARCHAR2;
table_space_name VARCHAR2;
object_name VARCHAR2;
sub_object_name VARCHAR2;
error_txt VARCHAR2;
msg_body VARCHAR2;
ret_value BOOLEAN;
mail_conn UTL_SMTP.CONNECTION;
BEGIN
--Get session ID
SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT;
--Get instance number
cur_inst := userenv('instance');
--Get space error information
ret_value :=
DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,
table_space_name,object_name, sub_object_name);
/*
--If the error is related to undo segments, log error, send email
--to DBA, and abort the statement. Otherwise, set timeout to 8 hours.
--
--sys.rbs_error is a table which is to be
--created by a DBA manually and defined as
--(sql_text VARCHAR2(1000), error_msg VARCHAR2(4000),
--suspend_time DATE)
*/
IFOBJECT_TYPE = 'UNDO SEGMENT' THEN
/* LOG ERROR */
INSERT INTO sys.rbs_error (
SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
FROM DBMS_RESUMABLE
WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst
);
SELECT ERROR_MSG INTO error_txt FROM DBMS_RESUMABLE
WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;
-- Send email to receipient through UTL_SMTP package
msg_body:='Subject: Space Error Occurred
Space limit reachedfor undo segment ' || object_name ||
on ' || TO_CHAR(SYSDATE,'Month dd, YYYY, HH:MIam') ||
'. Error message was ' ||error_txt;
mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);
UTL_SMTP.HELO(mail_conn, 'localhost');
UTL_SMTP.MAIL(mail_conn, 'sender@localhost');
UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');
UTL_SMTP.DATA(mail_conn, msg_body);
UTL_SMTP.QUIT(mail_conn);
-- Abort the statement
DBMS_RESUMABLE.ABORT(cur_sid);
ELSE
-- Set timeout to 8 hours
DBMS_RESUMABLE.SET_TIMEOUT(28800);
END IF;
/* commit autonomous transaction */
COMMIT;
END;
/
三．示例：
MOS 上的一篇使用示例：
Using RESUMABLESession to Avoid Transaction Abort Due to Space Errors [ID 136941.1]
3.1 The user running thetransaction requires the RESUMABLE system privilege :
SQL> conn system/oracle
Connected.
SQL> grant resumable to scott;
Grant succeeded.
resumable权限也被包含在其他role里面，比如dba，当一个用户拥有dba role时，就自动拥有了resumable privilege.
3.2 Set the session sothat the following transactions might be resumed in case of interruption due tospace 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 'altersession enable resumable timeout 1200';
6 end;
7 /
Trigger created.
3.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_RESUMABLEview:
3.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.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 tableSCOTT.TEST_RESUMABLE
3.3.3 The statement mayissue 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 whythe session hangs, and needs to find which action to take to alleviate theora-1631 error:
SQL> connect system/manager
Connected.
SQL> alter table scott.test_resumable storage (maxextents 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 doesnot 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
