管理可恢复空间
一、可恢复空间分配的概述
1、可恢复语句如何工作：
① 客户使用ALTER SESSION语句明确启用可恢复时才可以执行恢复
② 以下情况下，可恢复语句被暂停：
* 没有空间条件
* 达到最大盘区条件
* 超过空间限额条件
③ 可恢复语句执行被暂停期间，采用以下动作：
* 修改日志中报告错误
* 若在AFTER SUSPEND系统事件上注册触发器，则触发器执行
(使用DBMS_RESUMABLE包和DBA_RESUMABLE视图访问错误信息数据)
④ 暂停一个语句自动导致暂停该事务。(暂停到恢复期间所有事务资源被保持)
⑤ 出错条件消失时，暂停语句自动恢复执行
⑥ 使用DBMS_RESUMABLE.ABORT()过程，可强制暂停语句发出异常
⑦ 暂停超过时间间隔与可恢复语句相关联(例如超过2小时的可恢复语句被唤醒并报错)
⑧ 可恢复语句在执行期间可被多次暂停和恢复。
注：可恢复方式不支持远程操作。
2、可恢复的操作
① 查询：
用到临时空间(排序区)的SELECT语句是可恢复的。
当使用OCI时OCIStmtExecute()和OCIStmtFetch()调用是可恢复的。
② DML：
包括INSERT、UPDATE、DELETE，以及来自外部表的INSERT ONTO ... SELECT
与执行它们的接口无关，可以是OCI、JSQL、PL/SQL或其他接口
③ 导入/导出
SQL*Loader的命令行出现可更改错误之后是可恢复的
④ DDL：以下语句是可恢复的：
① 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
3、哪些错误是可改正的
① 没有空间条件
② 达到最大盘区条件
③ 超过空间限额条件
4、字典管理的表空间可恢复空间分配的限制
① CREATE TABLE 或 CREATE INDEX这样的DDL操作执行时，使用了MAXEXTENTS参数，会导致空间用完的情况。此时操作不会被暂停，而是被放弃。因为在初期创建的时候无法进行修改。而如果是DML操作增加或修改了MAXEXTENTS参数，则会被暂停并在以后恢复。注意可以使用MAXEXTENTS UNLIMITED或使用本地管理的表空间来克服。
② 回滚段位于字典管理的表空间中时，对回滚段的空间分配是不可恢复的。但是对于用户对象的空间按分配仍然可恢复。要克服这种限制，建议使用自动撤销管理或将回滚段放置在本地管理的表空间中。
二、启用和禁用可恢复空间分配
1、语句格式：
ALTER SESSION ENABLE RESUMABLE; --启用
ALTER SESSION DISABLE RESUMABLE; --禁用
2、指定超时间隔
超时间隔表示超过该间隔时间如果没有采取干预，暂停语句会出错。
① 一种方法是使用SQL语句来进行修改：
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600; --默认7200秒
② 另一种方法可以用触发器来设置，如：
CREATE OR REPLACE TRIGGER resumable_default_timeout
AFTER SUSPEND
ON DATABASE
BEGIN
DBMS_RESUMABLE.SET_TIMEOUT(3600);
END;
3、可恢复语句命名
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'insert into table';
NAME的默认值是：User USERNAME(USERID), Session SESSIONID, Instance INSTANCEID
NAME用于在DBA_RESUMABLE和USER_RESUMABLE视图中标识该可恢复语句
三、检测暂停的语句
因为可恢复语句被暂停时，客户端并不会报错，为了采取纠正动作，需要检测暂停语句。
1、AFTER SUSPEND系统事件和触发器
在AFTER SUSPEND触发器中，可以使用USER_RESUMABLE或DBA_RESUMABLE视图，或者DBMS_RESUMABLE.SPACE_ERROR_INFO函数来获取关于可恢复语句的信息，以及用来放弃暂停的语句或调节可恢复超时间隔的值。
2、在视图中查看
DBA_RESUMABLE：包含当前正在执行或被暂停的可恢复语句的行。
V$SESSION_WAIT：语句被暂停即进入等待状态，选择EVENT值为“susprended on space error”的行。
3、DBMS_RESUMABLE包介绍
① ABORT(sessionID)：
放弃一个暂停的可恢复语句。sessionID是该语句在其执行的会话的标识。对于并行的DML/DDL，则取任何一个sessionID。
可以在AFTER SUSPEND触发器内调用，也可以在触发器外调用。
② GET_SESSION_TIME3OUT(sessionID)：
为使用sessionID的会话返回可恢复语句的当前超时间隔，单位秒。会话不存在时返回-1。
③ SET_SESSION_TIMEOUT(sessionID,timeout)：
为sessionID设置超时间隔，会话不存在时则不做任何动作。
④ GET_TIMEOUT()：
返回当前会话的可恢复语句当前超时间隔值。
⑤ SET_TIMEOUT(timeout)：
为当前会话的可恢复语句设置一个超时间隔。
四、可恢复空间分配的例子
建立一个触发器，其有以下两个作用：
1、如果回滚段达到它的空间限额，那么一条消息发给DBA且该语句被放弃
2、如果任何其他的可改正错误发生，超时间隔被重置为8小时
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
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
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 rollback segment, log error, send email
-- to DBA, and abort the segment. Otherwise, set timeout to a8 hours.
--
-- sys.rbs_error is created by DBA manually and defined as
-- sql_text VARCHAR2(1000), error_msg VARCHAR2(4000),
-- suspend_time DATE
*/
IF
OBJECT_TYPE =
'ROLLBACK 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
INSRANCE_ID = cur_inst;
-- Send email to receipient via UTL_SMTP package
msg_body :=
'Subject: Space Error Occurred
Space limit reached for rollback 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 segment
DBMS_RESUMABLE.ABORT(cur_sid);
ELSE
-- Set timeout to 8 hours
DBMS_RESUMABLE.SET_TIMEOUT(
28800
);
END
IF
;
/* commit autonomous transaction */
COMMIT
;
END
;
-The End-