oracle 数据库对象自动备份

      本文介绍一种数据库对象备份的方法。其原因是因为我在开发时有两个数据库用户，他们的数据表结构相同，所开发的存储过程也相同，但是数据却是不同地方的数据。因为懒惰所以在修改过程包时只想修改一次就ok，所以想了一个半自动的办法，让数据库自动备份数据库对象，然后编写bat文件进行同步更新。

      首先要明确主次，我把a数据库用户作为修改对象，b数据库用户只是同步更新。ok

      步骤：

      一、用dba身份登录数据库，给a数据库用户指定一个存储路径‘d:\test’，并授予读写的权限。

create or replace directory D_OUTPUT as 'D:\test';   
grant read,write on directory D_OUTPUT to a;   
GRANT EXECUTE ON utl_file TO a; 

 

    二、用a登录数据库，编写自动备份包的过程。

   

create or replace procedure save_PACKAGEtoSQL_thomas is
  procedname varchar2(32);
  i          PLS_INTEGER := 0;
  L_output   utl_file.file_type;
  file_dic   varchar2(200) := 'D_OUTPUT'; --目录
  file_name  varchar2(32) := 'pro.sql'; --生成的文件
  cursor proc is --得到每个业务模块中用到的存储过程名称
    select x.referenced_name
      from user_dependencies x
     where x.referenced_type = 'PACKAGE'
     group by x.referenced_name;
  
begin
  --打开文件
  L_output := utl_file.fopen(file_dic, file_name, 'a');
  --循环得到每一个存储过程名称
  open proc;
  loop
    fetch proc
      into procedname;
    exit when proc%notfound;
    i := i + 1;
    dbms_output.put_line('procedname' || i || '=' || procedname);
  
    --得到每一个存储过程的source
    for j in (select decode(t.line,
                            1,
                            'Create or replace ' || t.text,
                            t.text) text
                from user_source t
               where t.name = procedname
                 and t.type = 'PACKAGE'
               order by line) LOOP
      --写每一个存储过程包到文件
      UTL_FILE.put_line(L_output, j.text, false);
    END LOOP;
    --在每一个存储过程后增加'/'
    UTL_FILE.put_line(L_output, '/', false);
    for j in (select decode(t.line,
                            1,
                            'Create or replace ' || t.text,
                            t.text) text
                from user_source t
               where t.name = procedname
                 and t.type = 'PACKAGE BODY'
               order by line) LOOP
      --写每一个存储过程包体到文件
      UTL_FILE.put_line(L_output, j.text, false);
    END LOOP;
    --在每一个存储过程后增加'/'
    UTL_FILE.put_line(L_output, '/', false);
  end loop;

  close proc;

  UTL_FILE.fclose(L_output);
end save_PACKAGEtoSQL_thomas;

   三、编写job，定时每天晚上12点执行。

  

VARIABLE job_busilog_addpartition NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:job_busilog_addpartition,save_PACKAGEtoSQL_thomas;',trunc(sysdate)+23/24,'SYSDATE+1');
COMMIT;
END;

  四、编写bat文件，内容：start sqlplus     b/pwd@db   @ d:\test\pro.sql  。

 

  完成。

======================================================================

  后续问题：1、上面是本地数据库思路，远程数据库需要数据库连接就可以了。

                 2、本来准备自动完成更新的，但是不知道怎样备份成dmp文件，也或者是在过程里怎样实现导入sql文件导入。

                 3、其实上面只是对包进行备份，也可以对所有用户对象进行备份。

  

  

   

 

评论

1 楼 tjaol 2010-09-20  

如果备份不在包里的存储过程，怎么写？ 还用 x.referenced_type = 'PACKAGE'  么？  
select x.referenced_name  
    from user_dependencies x  
      where x.referenced_type = 'PACKAGE' 
       group by x.referenced_name; 

下面怎么写？
其他的对象怎么写？ 例如sequence, database link