利用可恢复空间分配技术自动分配表空间
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;
