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

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 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 where object_name=v_tname;

if v_cnt=0 then

insert into ) 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  where object_name=v_tname;

if v_target_ddl_time is null then

update set tar=v_target_ddl_time  where object_name=v_tname;

commit;

execute immediate 'create table '||v_tname||' as select * from where 1=2 ';

commit;

end if;

select tar into v_target_ddl_time from where object_name=v_tname;

if v_last_ddl_time<=v_target_ddl_time then

--说明表未变化

null;

else

--说明表结构变化了

--备份此表

--create table t_test as select * from 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 where 1=2 ';

dbms_output.put_line(v_sql);

commit;

end if;

end if;

else

execute immediate 'create table '||v_tname||' as select * from where 1=2 ';

commit;

select last_ddl_time into v_target_ddl_time from user_objects where object_name=v_tname;

commit;

update 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,先编写大处理逻辑,再细分每人大处理逻辑为子处理逻辑.