利用可恢復(fù)空間分配技術(shù)自動分配表空間

Oracle可恢復(fù)空間分配技術(shù)讓我們可以通過創(chuàng)建一個(gè)在會話掛起時(shí)自動運(yùn)行的after suspend on database觸發(fā)器處理問題，如通過電子郵件報(bào)告掛起事件、檢查并自動修復(fù)掛起故障等，如可以在插入數(shù)據(jù)導(dǎo)致表空間容量不足時(shí)，通過觸發(fā)器程序判斷并自動給表空間分配容量。以下例子給出這方面的應(yīng)用。

一、可恢復(fù)空間自動分配功能的部署

因?yàn)閍fter suspend on database觸發(fā)器中不允許調(diào)用DDL語句，因此空間分配的操作不能通過觸發(fā)器調(diào)用來執(zhí)行。這里的辦法就是通過調(diào)度程序來啟動作業(yè)，并且這個(gè)調(diào)度作業(yè)必須是基于事件觸發(fā)的。

通過設(shè)置實(shí)例參數(shù)resumable_timeout為所有會話啟用可恢復(fù)空間。這是一個(gè)動態(tài)參數(shù)，如設(shè)置會話在遇到空間問題時(shí)掛起1分鐘

alter system set resumable_timeout = 60;

創(chuàng)建一個(gè)用來進(jìn)行可恢復(fù)空間管理的用戶并授權(quán)

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

創(chuàng)建一個(gè)存放可恢復(fù)空間分配的SQL語句的表

create table resumable_sql(sql_text varchar2(200));

定義一個(gè)記錄消息信息的類型

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

/

創(chuàng)建隊(duì)列表用于記錄消息，指定表名和消息的類型名

begin

  dbms_aqadm.create_queue_table(queue_table        => 'event_queue_table',

                                queue_payload_type => 'event_queue_type',

                                multiple_consumers => true);

end;

/

創(chuàng)建消息隊(duì)列，指定隊(duì)列名和隊(duì)列表

begin

  dbms_aqadm.create_queue(queue_name  => 'event_queue',

                          queue_table => 'event_queue_table');

end;

/

啟動隊(duì)列

begin

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

end;

/

創(chuàng)建一個(gè)錯(cuò)誤日志表，對程序發(fā)生的錯(cuò)誤進(jìn)行定位

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

創(chuàng)建執(zhí)行空間分配的存儲過程

create or replace procedure alloc_space authid current_user is

  my_count number;

  my_sql   varchar2(200);

begin

  -- 獲取空間分配的執(zhí)行語句

  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;

    -- 執(zhí)行空間分配

    execute immediate my_sql;

    delete from resumable_sql;

    commit;

  end if;

exception

  when others then

    -- 記入錯(cuò)誤日志

    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;

/

創(chuàng)建執(zhí)行空間分配的程序

begin

  dbms_scheduler.create_program(program_name   => 'alloc_space_pro',

                                program_type   => 'stored_procedure',

                                program_action => 'alloc_space',

                                enabled        => true);

end;

/

創(chuàng)建執(zhí)行空間分配的調(diào)度作業(yè)，該作業(yè)是由事件觸發(fā)的

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;

/

創(chuàng)建存儲過程，檢查是否存在可恢復(fù)空間掛起的會話，生成分配空間的DDL語句，發(fā)送空間分配事件到消息隊(duì)列

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

  -- 檢查是否存在可恢復(fù)空間掛起的會話

  select count(*)

    into my_count

    from dba_resumable

   where status = 'SUSPENDED';

  if my_count != 0 then

    -- 獲取錯(cuò)誤編號

    select error_number

      into my_err_number

      from dba_resumable

     where rownum = 1;

    -- 是否是因?yàn)楸砜臻g容量不足引起的掛起

    if my_err_number = 1653 then

      -- 獲取表空間名

      select error_parameter4

        into my_err_tablespace

        from dba_resumable

       where error_number = 1653

         and rownum = 1;

      -- 可處理的表空間應(yīng)當(dāng)是用戶定義的表空間

      if my_err_tablespace = v_tablespace then

        -- 生成該表空間的最后數(shù)據(jù)文件之后的新數(shù)據(jù)文件名

        -- 文件應(yīng)按照兩位數(shù)字規(guī)則命名，如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);

        -- 生成可恢復(fù)空間分配的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;

        -- 發(fā)送空間分配事件到消息隊(duì)列中通知調(diào)度程序作業(yè)進(jìn)行空間分配

        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

    -- 記入錯(cuò)誤日志

    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;

/

創(chuàng)建after suspend on database觸發(fā)器，當(dāng)數(shù)據(jù)庫掛起時(shí)執(zhí)行對空間分配問題的檢查和處理

create or replace trigger sus_tri

  after suspend on database

begin

  sus_tri_pro('TEST', 4);

end;

/

二、運(yùn)行效果測試

創(chuàng)建表空間和表

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

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

查看表空間數(shù)據(jù)文件

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

授予會話可恢復(fù)空間分配的權(quán)限

grant resumable to scott;

連接到scott用戶

conn scott/tiger

向表中插入數(shù)據(jù)

begin

  for i in 1 .. 2000 loop

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

  end loop;

  commit;

end;

/

可以看到，因?yàn)楸砜臻g不足，會話發(fā)生了少許等待，隨后執(zhí)行空間分配的調(diào)度程序被啟動，完成空間分配后，操作得以完成。

查看表空間數(shù)據(jù)文件，系統(tǒng)自動分配了新的數(shù)據(jù)文件

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

查看空間分配的調(diào)度作業(yè)成功執(zhí)行

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;

如果要去除可恢復(fù)空間功能的部署，則直接刪除管理用戶及其所有對象即可

conn / as sysdba

drop user alloc cascade;