resumable space allocation

什么是resumable？

Oracle Database provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables you to take corrective action instead of the Oracle Database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes. This feature is calledresumable space allocation.

默认情况下oracle没有启用此功能.需要手动开启.

What Operations are Resumable?

The following operations are resumable:哪些情况下会用到resumable特性

• Queries

  SELECT statements that run out of temporary space (for sort areas) are candidates for resumable execution. When using OCI, the callsOCIStmtExecute() andOCIStmtFetch() are candidates.

• DML

  INSERT, UPDATE, and DELETE statements are candidates. The interface used to execute them does not matter; it can be OCI, SQLJ, PL/SQL, or another interface. Also,INSERT INTO...SELECT from external tables can be resumable.

• Import/Export

  As for SQL*Loader, a command line parameter controls whether statements are resumable after recoverable errors.

• DDL

  The following statements are candidates for resumable execution:

  • CREATE TABLE ... AS SELECT

  • CREATE INDEX

  • ALTER INDEX ... REBUILD

  • ALTER TABLE ... MOVE PARTITION

  • ALTER TABLE ... SPLIT PARTITION

  • ALTER INDEX ... REBUILD PARTITION

  • ALTER INDEX ... SPLIT PARTITION

  • CREATE MATERIALIZED VIEW

  • CREATE MATERIALIZED VIEW LOG

如何启用resumable？

alter system set resumable_timeout=3600;//这个是挂起时间 单位是s

alter session enable resumable;

alter session enable resumable timeout 3600;

如何停用resumable?

alter session disable resumable;

alter system set resumable_timeout=0;

Using Views to Obtain Information About Suspended Statements .用下面的视图来获取关于挂起的信息

The following views can be queried to obtain information about the status of resumable statements:

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 theEVENT column containing "statement suspended, wait error to be cleared".

下面举个列子:

1：创建个表空间和用户

SQL> create tablespace testresum datafile '/u01/app/oracle/oradata/orc1/testresum.dbf' size 100m;
表空间已创建。

SQL> create user resumuser identified by bayue default tablespace testresum quota 2m on testresum;
用户已创建。

SQL> grant create session,resumable,create table,select any dictionary to resumuser;
授权成功。

2:用创建好了的用户登录来实验resumable

SQL> show user  
USER 为 "RESUMUSER"  

SQL> alter session enable resumable timeout 60;  
会话已更改。

SQL> set time on;
22:25:52 SQL>create table ttt as select * from dba_objects;
create table ttt as select * from dba_objects 
				  *
第 1 行出现错误:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace  'TESTRESUM'
22:26:58 SQL>  这里就挂起60秒,60秒过了之后就报错了，因为空间不够才给2m。所以在这个设置的超时时间内去加大空间就可以继续执行创建表的语句而不会报错.

22:26:58 SQL> alter session disable resumable;停用resumable
会话已更改。

22:29:44 SQL>create table ttt as select * from dba_objects;
create table ttt as select * from dba_objects 
                                  *
第 1 行出现错误:
ORA-01536: space quota exceeded for tablespace 'TESTRESUM'
22:29:49 SQL>立马就会报错。不会有时间去处理问题。所以有了resumable特性之后很方便。