利用可恢復空間分配技術自動分配表空間

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 hh34: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;