利用可恢复空间分配技术自动分配表空间

Oracle可恢复空间分配技术让我们可以通过创建一个在会话挂起时自动运行的after suspend on database触发器处理问题，如通过电子邮件报告挂起事件、检查并自动修复挂起故障等，如可以在插入数据导致表空间容量不足时，通过触发器程序判断并自动给表空间分配容量。以下例子给出这方面的应用。

一、可恢复空间自动分配功能的部署

因为after suspend on database触发器中不允许调用DDL语句，因此空间分配的操作不能通过触发器调用来执行。这里的办法就是通过调度程序来启动作业，并且这个调度作业必须是基于事件触发的。

通过设置实例参数resumable_timeout为所有会话启用可恢复空间。这是一个动态参数，如设置会话在遇到空间问题时挂起1分钟

alter system set resumable_timeout = 60;

创建一个用来进行可恢复空间管理的用户并授权

conn / as sysdba

create user alloc identified by alloc;

grant connect, resource to alloc;

grant create job to alloc;

grant create trigger to alloc;

grant aq_administrator_role to alloc;

grant execute on dbms_aq to alloc;

grant select on dba_resumable to alloc;

grant select on dba_data_files to alloc;

grant dba to alloc;

连接到alloc用户

conn alloc/alloc

创建一个存放可恢复空间分配的SQL语句的表

create table resumable_sql(sql_text varchar2(200));

定义一个记录消息信息的类型

create or replace type event_queue_type as object(event_name varchar2(30));

/

创建队列表用于记录消息，指定表名和消息的类型名

begin

  dbms_aqadm.create_queue_table(queue_table        => 'event_queue_table',

                                queue_payload_type => 'event_queue_type',

                                multiple_consumers => true);

end;

/

创建消息队列，指定队列名和队列表

begin

  dbms_aqadm.create_queue(queue_name  => 'event_queue',

                          queue_table => 'event_queue_table');

end;

/

启动队列

begin

  dbms_aqadm.start_queue(queue_name => 'event_queue');

end;

/

创建一个错误日志表，对程序发生的错误进行定位

create table err_logs(proc_name varchar2(50), log_time date, error_stack varchar2(200), error_backtrace varchar2(200));

创建执行空间分配的存储过程

create or replace procedure alloc_space authid current_user is

  my_count number;

  my_sql   varchar2(200);

begin

  -- 获取空间分配的执行语句

  select count(*) into my_count from resumable_sql;

  if my_count != 0 then

    select sql_text into my_sql from resumable_sql where rownum = 1;

    -- 执行空间分配

    execute immediate my_sql;

    delete from resumable_sql;

    commit;

  end if;

exception

  when others then

    -- 记入错误日志

    insert into err_logs

      (proc_name, log_time, error_stack, error_backtrace)

    values

      ('alloc_space',

       sysdate,

       dbms_utility.format_error_stack,

       dbms_utility.format_error_backtrace);

    commit;

end;

/

创建执行空间分配的程序

begin

  dbms_scheduler.create_program(program_name   => 'alloc_space_pro',

                                program_type   => 'stored_procedure',

                                program_action => 'alloc_space',

                                enabled        => true);

end;

/

创建执行空间分配的调度作业，该作业是由事件触发的

begin

  dbms_scheduler.create_job(job_name        => 'alloc_space_job',

                            program_name    => 'alloc_space_pro',

                            start_date      => sysdate,

                            event_condition => 'tab.user_data.event_name = ''alloc_space_event''',

                            queue_spec      => 'event_queue',

                            enabled         => true);

end;

/

创建存储过程，检查是否存在可恢复空间挂起的会话，生成分配空间的DDL语句，发送空间分配事件到消息队列

create or replace procedure sus_tri_pro(v_tablespace varchar2,

                                        v_file_size  number) authid current_user is

  my_count              number;

  my_err_number         number;

  my_err_tablespace     varchar2(50);

  my_filename           varchar2(200);

  my_sql                varchar2(200);

  my_enqueue_options    dbms_aq.enqueue_options_t;

  my_message_properties dbms_aq.message_properties_t;

  my_message_handle     raw(16);

  my_queue_msg          event_queue_type;

begin

  -- 检查是否存在可恢复空间挂起的会话

  select count(*)

    into my_count

    from dba_resumable

   where status = 'SUSPENDED';

  if my_count != 0 then

    -- 获取错误编号

    select error_number

      into my_err_number

      from dba_resumable

     where rownum = 1;

    -- 是否是因为表空间容量不足引起的挂起

    if my_err_number = 1653 then

      -- 获取表空间名

      select error_parameter4

        into my_err_tablespace

        from dba_resumable

       where error_number = 1653

         and rownum = 1;

      -- 可处理的表空间应当是用户定义的表空间

      if my_err_tablespace = v_tablespace then

        -- 生成该表空间的最后数据文件之后的新数据文件名

        -- 文件应按照两位数字规则命名，如TEST01.DBF、TEST02.DBF...

        select replace(file_name,

                       substr(file_name, -6, 2),

                       trim(to_char(to_number(substr(file_name, -6, 2)) + 1,

                                    '00')))

          into my_filename

          from dba_data_files

         where file_id = (select max(file_id)

                            from dba_data_files

                           where tablespace_name = v_tablespace);

        -- 生成可恢复空间分配的SQL语句

        my_sql := 'alter tablespace ' || v_tablespace || ' add datafile ''' ||

                  my_filename || ''' size ' || v_file_size || 'm';

        -- SQL语句插入表中等待处理

        delete from resumable_sql;

        insert into resumable_sql (sql_text) values (my_sql);

        commit;

        -- 发送空间分配事件到消息队列中通知调度程序作业进行空间分配

        my_queue_msg := event_queue_type('alloc_space_event');

        dbms_aq.enqueue(queue_name         => 'alloc.event_queue',

                        enqueue_options    => my_enqueue_options,

                        message_properties => my_message_properties,

                        payload            => my_queue_msg,

                        msgid              => my_message_handle);

      end if;

    end if;

  end if;

exception

  when others then

    -- 记入错误日志

    insert into err_logs

      (proc_name, log_time, error_stack, error_backtrace)

    values

      ('sus_tri_pro',

       sysdate,

       dbms_utility.format_error_stack,

       dbms_utility.format_error_backtrace);

    commit;

end;

/

创建after suspend on database触发器，当数据库挂起时执行对空间分配问题的检查和处理

create or replace trigger sus_tri

  after suspend on database

begin

  sus_tri_pro('TEST', 4);

end;

/

二、运行效果测试

创建表空间和表

create tablespace test datafile 'd:/oradata/mes/test01.dbf' size 2m;

create table scott.t1(c1 char(1000)) tablespace test;

查看表空间数据文件

col file_name for a30

select file_name, bytes from dba_data_files where tablespace_name = 'TEST';

FILE_NAME                           BYTES

------------------------------ ----------

D:/ORADATA/MES/TEST01.DBF         2097152

授予会话可恢复空间分配的权限

grant resumable to scott;

连接到scott用户

conn scott/tiger

向表中插入数据

begin

  for i in 1 .. 2000 loop

    insert into scott.t1 values ('a row');

  end loop;

  commit;

end;

/

可以看到，因为表空间不足，会话发生了少许等待，随后执行空间分配的调度程序被启动，完成空间分配后，操作得以完成。

查看表空间数据文件，系统自动分配了新的数据文件

conn / as sysdba

col file_name for a30

select file_name, bytes from dba_data_files where tablespace_name = 'TEST';

FILE_NAME                           BYTES

------------------------------ ----------

D:/ORADATA/MES/TEST01.DBF         2097152

D:/ORADATA/MES/TEST02.DBF         4194304

查看空间分配的调度作业成功执行

col owner for a10

col job_name for a20

col status for a10

col run_duration for a20

select *

  from (select owner,

               job_name,

               status,

               to_char(actual_start_date, 'yyyy-mm-dd hh24:mi:ss') actual_start_date,

               run_duration

          from dba_scheduler_job_run_details

         where job_name = 'ALLOC_SPACE_JOB'

         order by actual_start_date desc)

 where rownum < 10;

OWNER      JOB_NAME             STATUS     ACTUAL_START_DATE   RUN_DURATION

---------- -------------------- ---------- ------------------- --------------------

ALLOC      ALLOC_SPACE_JOB      SUCCEEDED  2018-02-07 12:05:32 +000 00:00:00

测试完成做清理

drop table scott.t1 purge;

drop tablespace test including contents and datafiles;

如果要去除可恢复空间功能的部署，则直接删除管理用户及其所有对象即可

conn / as sysdba

drop user alloc cascade;