通过oracle 数据库链接db link自动化备份生产库的脚本存储过程

最新推荐文章于 2022-05-20 15:31:44 发布

clg10051

最新推荐文章于 2022-05-20 15:31:44 发布

阅读量112

点赞数

文章标签：数据库

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;