(12)空间不足,留个亡羊补牢的机会给自己。

最新推荐文章于 2022-11-25 10:56:09 发布

拙_言

最新推荐文章于 2022-11-25 10:56:09 发布

阅读量1.9k

点赞数

分类专栏： DBA与项目文章标签： RESUMABLE 表空间用尽表空间管理

本文链接：https://blog.csdn.net/xcl168/article/details/17194401

版权

DBA与项目专栏收录该内容

61 篇文章 0 订阅

订阅专栏

在日常项目维护中 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;

5. 再去执行插入语句，应当会报错，不过这一次应当可以在 user_resumable 中看到它了，并且adrci中也能查到这个错误。

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;

这种非表级的Trigger要小心，代码检查通过，但执行有问题就会报 ORA-00604: 递归 SQL 级别 1 出现错误，所以写好后，最后 disconnect;后再conn 用户一下看正常不.

至此，就真正让这个特性落地，可以实用了。

MAIL: xcl_168@aliyun.com

Blog: http://blog.csdn.net/xcl168