在日常项目维护中
DBA会经常主动地去检查表空间是否够用，但数据库还是能搞出空间问题。我在后面会列一些常
见的可能让你的表空间脱出掌握的操作。而我今天说的这个特性可好了，它可以让你在出现空间问题时有足够的时间
去解决它，让业务继续。不是表空间的自动扩展(autoextent),它有它的问题列情况时有说明.
列一些可以会出现空间问题的情况:
1. 一条数据量很大的SELECT,把临时表空间给用尽了。
2. 大批量的DELETE，让撤销表空间空间不足。
3.临时或定时的sqlldr,因对数据量估计不足，导致表空间用完。
4.采用祼设备的表空间，空间不足。
5.物理存储上的空间不足了，表空间自动扩展失败。
6. 操作系统文件的4GB限制，不过这个现在应当很少见了。
7. 表空间分配给表的空间用完了。
8. ....
列的这些情况中，有些通过细心的检查是可以规避的，但有些确实是容易忽略或规避不了的。而如果发生了这种
现象，后果就不说了，为给了DBA们找条生路，Oracle提供了一个叫
可恢复的空间分配(RESUMABLE)特性。
它可以
在发生空间不足时，让正在执行的操作挂起，只要在设定的时间内(默认为2小时)解决空间问题后，操作如常继续。
设置步骤:
1. 在系统级别启用它，让所有会话都可以享受这个功能。 (仅11g及以上可用)
-- 设置为 7200 秒即2小时 ,动态参数，无需库重启。
-- 如把值设为 0 ,即禁用些特性
SQL> alter system set resumable_timeout = 7200;
系统已更改。
SQL> show parameter resumable_timeout
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout integer 7200
2. 在session级别启用它
2.1 授权(dba用户自带就有)
grant resumable to xcl;
2.2 在 session 中启用它
--启用
alter session enable resumable timeout 10000;
--禁用
alter session disable resumable;
3. 当出现问题时，可以在dba_resumable或用户的user_resumable中查到启用了这个功能的会话的状态及SQL语句。
即哪些会话及SQL被挂起了。 挂起时 status值为SUSPENDED. 正常后为 NORMAL.
SELECT user_id,session_id,status,name,sql_text FROM dba_resumable;SELECT session_id,status,name,sql_text FROM user_resumable;
--查看参数
show parameter resumable_timeout
如何测试?
1.新建一个空间很小的测试表空间，并将autoextend 关掉.
create table testrs_data datafile '路径' size 1m autoextend off;
2. 新增一个用户，并使用这个表空间,并授resumable权限
create user testrs identifyed by testrs ;
grant connect to testrs;
grant resource to testrs;
grant resumable to testrs;
3. 在测试用户下新增一个表，并不停地向里面插入数据，直到把空间用完为止。
--简单点可以建个带blob类型的表，连接插入几个几百KB的文件就行了。在这就不举例了。
4. 启用可恢复表空间分配
--可挂起1个小时
alter session enable resumable timeout 3600;
SELECT user_id,session_id,status,name,sql_text FROM dba_resumable;
或
SELECT session_id,status,name,sql_text FROM user_resumable;
6.把表空间扩下容
alter tablespace testrs_data add datafile '路径' size 1m;
7. 再去执行插入语句，这次应当顺利通过, 视图中的status应当变为了NORMAL.
除了上面的方式，Oracle还提供了一个包dbms_resumable作为辅助，在工具下，可以去包定义里面的函数相应应当都懂。
不过手工在session真处理的方式，在实际中并不实用，顶多用来做做测试，
真正要使用，需要利用数据库trigger来实现看下面:
1. 在schema 级别,让用户在登录时就设置
--在schema 级别,让用户在登录时就设置
create or replace trigger trg_db_logon_sets
after logon on demo.schema
declare
-- local variables here
begin
--前提是用户有resumable权限
--启用可恢复表空间分配
execute immediate 'alter session enabled resumable timeout 7200';
end trg_db_logon_sets;
2.
在数据库 after suspend事件中加个trigger,一有挂起发生就发邮件
create or replace trigger trg_db_resumable_notify
after suspend on database
declare
-- local variables here
v_dba_resumable dba_resumable%rowtype;
v_sessionsid NUMBER;
v_inst NUMBER;
v_err_type VARCHAR2(255);
v_object_owner VARCHAR2(80);
v_object_type VARCHAR2(80);
v_table_space_name VARCHAR2(80);
v_object_name VARCHAR2(80);
v_sub_object_name VARCHAR2(80);
v_ret BOOLEAN;
begin
--得到当前session ID
SELECT DISTINCT(SID) INTO v_sessionsid FROM V$MYSTAT;
--实例序号
v_inst := userenv('instance');
--得到挂起空间错误信息,可以用来做些区分处理,暂时不用
-- if(v_object_owner == 'DEMO') dbms_resumable.set_timeout(7200 * 2); end;
-- if(v_object_type == 'UNDO SEGMENT')
v_ret := DBMS_RESUMABLE.SPACE_ERROR_INFO(v_err_type,v_object_type,v_object_owner,
v_table_space_name,v_object_name, v_sub_object_name);
SELECT * INTO v_dba_resumable
FROM dba_resumable
WHERE session_id = v_sessionsid
AND instance_id = v_inst
AND status = 'SUSPENDED'
AND rownum < 2;
--发送邮件
--有些数据库可能报找不到utl_mail包的错误，这个包是要另外新建的。
utl_mail.send(sender=>'xcl_168@aliyun.com',
recipients=>'xxxx@aliyun.com',
subject=>'oracle resumable!!!',
cc=>'xxxx@aliyun.com,xxxx@aliyun.com',
bcc=>'xxxx@aliyun.com,xxxx@aliyun.com',
message=>'inst:'||v_inst||chr(13)||
' session sid:'||v_sessionsid||chr(13)||
' object_type:'||v_object_type||chr(13)||
' object_owner:'||v_object_owner||chr(13)||
' table_space_name:'||v_table_space_name||chr(13)||
' object_name:'||v_object_name||chr(13)||
' sub_object_name:'||v_sub_object_name||chr(13)||
' view error_msg:'||v_dba_resumable.error_msg );
end trg_db_logon_sets;
ORA-00604: 递归 SQL 级别 1 出现错误 ，所以写好后，最后 disconnect;后再conn 用户一下看正常不.
