Oracle可恢復(fù)空間分配技術(shù)讓我們可以通過(guò)創(chuàng)建一個(gè)在會(huì)話掛起時(shí)自動(dòng)運(yùn)行的after suspend on database觸發(fā)器處理問(wèn)題，如通過(guò)電子郵件報(bào)告掛起事件、檢查并自動(dòng)修復(fù)掛起故障等，如可以在插入數(shù)據(jù)導(dǎo)致表空間容量不足時(shí)，通過(guò)觸發(fā)器程序判斷并自動(dòng)給表空間分配容量。以下例子給出這方面的應(yīng)用。
創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、外貿(mào)網(wǎng)站建設(shè)與策劃設(shè)計(jì),大英網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:大英等地區(qū)。大英做網(wǎng)站價(jià)格咨詢:028-86922220
一、可恢復(fù)空間自動(dòng)分配功能的部署
因?yàn)閍fter suspend on database觸發(fā)器中不允許調(diào)用DDL語(yǔ)句，因此空間分配的操作不能通過(guò)觸發(fā)器調(diào)用來(lái)執(zhí)行。這里的辦法就是通過(guò)調(diào)度程序來(lái)啟動(dòng)作業(yè)，并且這個(gè)調(diào)度作業(yè)必須是基于事件觸發(fā)的。
通過(guò)設(shè)置實(shí)例參數(shù)resumable_timeout為所有會(huì)話啟用可恢復(fù)空間。這是一個(gè)動(dòng)態(tài)參數(shù)，如設(shè)置會(huì)話在遇到空間問(wèn)題時(shí)掛起1分鐘
alter system set resumable_timeout = 60;
創(chuàng)建一個(gè)用來(lái)進(jìn)行可恢復(fù)空間管理的用戶并授權(quán)
conn / as sysdba
create useralloc identified byalloc;
grant connect, resource toalloc;
grant create job toalloc;
grant create trigger to alloc;
grant aq_administrator_role toalloc;
grant execute on dbms_aq to alloc;
grant select on dba_resumable toalloc;
grant select on dba_data_files toalloc;
grant dba to alloc;
連接到alloc用戶
conn alloc/alloc
創(chuàng)建一個(gè)存放可恢復(fù)空間分配的SQL語(yǔ)句的表
create table resumable_sql(sql_text varchar2(200));
定義一個(gè)記錄消息信息的類(lèi)型
create or replace type event_queue_type as object(event_name varchar2(30));
/
創(chuàng)建隊(duì)列表用于記錄消息，指定表名和消息的類(lèi)型名
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;
/
啟動(dòng)隊(duì)列
begin
dbms_aqadm.start_queue(queue_name => 'event_queue');
end;
/
創(chuàng)建一個(gè)錯(cuò)誤日志表，對(duì)程序發(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í)行空間分配的存儲(chǔ)過(guò)程
create or replace procedure alloc_space authid current_user is
my_count number;
my_sql varchar2(200);
begin
-- 獲取空間分配的執(zhí)行語(yǔ)句
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)建存儲(chǔ)過(guò)程，檢查是否存在可恢復(fù)空間掛起的會(huì)話，生成分配空間的DDL語(yǔ)句，發(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ù)空間掛起的會(huì)話
select count(*)
into my_count
from dba_resumable
where status = 'SUSPENDED';
if my_count != 0 then
-- 獲取錯(cuò)誤編號(hào)
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語(yǔ)句
my_sql := 'alter tablespace ' || v_tablespace || ' add datafile ''' ||
my_filename || ''' size ' || v_file_size || 'm';
-- SQL語(yǔ)句插入表中等待處理
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ù)庫(kù)掛起時(shí)執(zhí)行對(duì)空間分配問(wèn)題的檢查和處理
create or replace trigger sus_tri
after suspend on database
begin
sus_tri_pro('TEST', 4);
end;
/
二、運(yùn)行效果測(cè)試
創(chuàng)建表空間和表
create tablespacetest datafile 'd:\oradata\mes\test01.dbf' size 2m;
create tablescott.t1(c1 char(1000)) tablespacetest;
查看表空間數(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
授予會(huì)話可恢復(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不足，會(huì)話發(fā)生了少許等待，隨后執(zhí)行空間分配的調(diào)度程序被啟動(dòng)，完成空間分配后，操作得以完成。
查看表空間數(shù)據(jù)文件，系統(tǒng)自動(dò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
測(cè)試完成做清理
drop table scott.t1 purge;
drop tablespace test including contents and datafiles;
如果要去除可恢復(fù)空間功能的部署，則直接刪除管理用戶及其所有對(duì)象即可
conn / as sysdba
drop user alloc cascade;
重慶分公司
重慶分公司
