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;

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;

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

相關文章