create or replace procedure proc_auto_backup
/************************************************************************************
编写者:翟勋杨
编写日期:2013-2-25
功能:定期备份库至专用备份库,且仅备份表结构及其它对象;
备份前判断,若未备份且备份已变更,则备份;否则不备份;
若备份超过一定期限,则自动删除所属用户
要点:1,构建一个表,用于存储源端所有的对象名称,存储过程会扫描它们,判断是否已备份此对象;
2,判断备份的表是否发生了变化,即:新增列,列变更,删除列;
user_tab_columns
新增列:多一条记录
列变更:
3,在源数据库创建 日志表:t_obj_log
4,在目标数据库创建自源数据库的db_link
5,再用dbms_scheduler调用此存储过程即可
6,明天要实现的:以变量式配置一个期限,过此期限则删除此用户或删除用户下对应的对象所有备份,以减少空间占用
7,实现其它对象,诸如存储过程,道理一样
*************************************************************************************/
AUTHID CURRENT_USER is
v_cnt pls_integer;--
cursor cur_source is select dl.object_name,
dl.created,
dl.last_ddl_time as source_ddl_time
from user_objects@db_link1 dl
where dl.object_type='TABLE' and
dl.object_name!='T_OBJ_LOG';
v_tname user_objects.object_name%type;
v_created user_objects.created%type;
v_last_ddl_time user_objects.last_ddl_time%type;
v_target_ddl_time user_objects.last_ddl_time%type;
v_sql varchar2(4000);
v_creation varchar2(100);
v_last varchar2(100);
begin
--在目标端的备份库查询是否已备份此表
--通过数据库链接查指定表是否
open cur_source;
loop
fetch cur_source into v_tname,v_created,v_last_ddl_time;
exit when cur_source%notfound;
--若日志表无匹配才插入记录,否则不插入数据
select count(object_name) into v_cnt from t_obj_log@db_link1 where object_name=v_tname;
if v_cnt=0 then
insert into t_obj_log@db_link1(OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME,CREATED) values(v_tname,'table',v_last_ddl_time,v_created);
commit;
end if;
select count(1) into v_cnt from tab where tname=v_tname;
if v_cnt>0 then
--已备份过此表
----进一步判断备份表是否变化过
--v_target_ddl_time如为null,则首次复制
select tar into v_target_ddl_time from t_obj_log@db_link1 where object_name=v_tname;
if v_target_ddl_time is null then
update t_obj_log@db_link1 set tar=v_target_ddl_time where object_name=v_tname;
commit;
execute immediate 'create table '||v_tname||' as select * from '||v_tname||'@db_link1 where 1=2 ';
commit;
end if;
select tar into v_target_ddl_time from t_obj_log@db_link1 where object_name=v_tname;
if v_last_ddl_time<=v_target_ddl_time then
--说明表未变化
null;
else
--说明表结构变化了
--备份此表
--create table t_test as select * from t_test@db_link1 where 1=2;
v_creation:=to_char(v_created,'yyyy-mm-dd hh24:mi:ss');
v_last:=to_char(v_last_ddl_time,'yyyymmddhh24miss');
--判断若已重命名则不进行操作
v_cnt:=0;
--必须添加'''',不然报无效标识符
---自第三次变化,即运行第三次时,会报错,因为目标库已经存在t_test和t_test_日期 2个表,此时下述代码仅处理t_test_日期,未处理t_test
v_sql:='select count(1) from user_objects where object_name='||''''||v_tname||v_last||'''';
execute immediate v_sql into v_cnt;
if v_cnt=0 then
execute immediate 'alter table '||v_tname||' rename to '||v_tname||v_last;
end if;
--故先判断是否存在t_test,若存在,先重命名,再创建它
select count(1) into v_cnt from user_objects where object_name=v_tname;
if v_cnt=0 then
execute immediate 'create table '||v_tname||' as select * from '||v_tname||'@db_link1 where 1=2 ';
dbms_output.put_line(v_sql);
commit;
end if;
end if;
else
execute immediate 'create table '||v_tname||' as select * from '||v_tname||'@db_link1 where 1=2 ';
commit;
select last_ddl_time into v_target_ddl_time from user_objects where object_name=v_tname;
commit;
update t_obj_log@db_link1 set tar=v_target_ddl_time where object_name=v_tname;
commit;
end if;
end loop;
close cur_source;
end;
小结:1,中间日志表,用于user_objects与此表的对应列查行比较,以判断对象是否
变化
2,构建自目标库到源库的db_link
3,基于db link的分布式事务,在存储过程代码中要显式编写commit;
4,使用cursor游标遍历整个源库的对象,和目标库进行比对.再进行下一步的动作
5,先编写大处理逻辑,再细分每人大处理逻辑为子处理逻辑.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-754717/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-754717/