Oracle数据库自动备份SQL文本：Procedure存储过程,View视图,Function函数,Trigger触发器,Sequence序列号等

功能：备份存储过程,视图,函数触发器,Sequence序列号等
准备工作：
--1.创建文件夹 ：'E:/OracleBackUp/ProcBack';--文本存放的路径
--2.执行：create or replace directory MyProcBakPath as 'E:/OracleBackUp/ProcBack';
--3.赋权限：
sqlplus /nolog
conn user/pswd as sysdba
grant select on DBA_OBJECTS to user;
--4.创建Job，自动执行，自动备份存储过程
+缩减代码,归类循环执行 lzpong 2015/03/18
+更改代码,使大存储过程也能保存,并且 不会出现 "用户名"."对象名" 的格式 lzpong 2015/07/14
+更改类型定义 为 long 字段类型表,使能保存大视图; 保存视图名到创建语句中 lzp 2016/05/06

+?触发器依然会有用户名的前缀?

以下是本人历经多次修改的最终Sql代码:

 1 CREATE OR REPLACE PROCEDURE OBJAUTOSTORE
 2 AS
 3 /* 功能：备份存储过程和视图
 4 准备工作：
 5 --1.创建文件夹 ：'E:/OracleBackUp/ProcBack';--文本存放的路径
 6 --2.执行：create or replace directory MyProcBakPath as 'E:/OracleBackUp/ProcBack';
 7 --3.赋权限：
 8 sqlplus /nolog
 9 conn nag/nag as sysdba
10 grant select on DBA_OBJECTS to NAG;
11 --4.创建Job，自动执行，自动备份存储过程
12 +缩减代码,归类循环执行 lzpong 2015/03/18
13 +更改代码,使大存储过程也能保存,并且 不会出现 "用户名"."对象名" 的格式  lzpong 2015/07/14
14 +更改类型定义 为 long 字段类型表,使能保存大视图; 保存视图名到创建语句中  lzp 2016/05/06
15 */
16 OutFile   UTL_FILE.FILE_TYPE;
17 --type user_source_table_type is table of user_source.text%TYPE INDEX BY BINARY_INTEGER;
18 type user_source_table_type is table of long INDEX BY BINARY_INTEGER;
19 user_source_table user_source_table_type;
20 
21 pos INTEGER;
22 line integer;
23 towner VARCHAR2(50) :='NAG';
24 
25 cursor abc is
26   SELECT 'PROCEDURE' otype,'Proc_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql' ofile FROM DUAL
27 union all SELECT 'VIEW' ,'View_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql'  FROM DUAL
28 union all SELECT 'TRIGGER' , 'Trig_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql'  FROM DUAL
29 union all SELECT 'SEQUENCE' , 'Sequ_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql'  FROM DUAL
30 union all SELECT 'FUNCTION' , 'Func_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql'  FROM DUAL
31 ;
32 BEGIN
33 
34 for rec in abc loop
35 dbms_output.put_line(rec.otype||','||rec.ofile);
36   OutFile:=UTL_FILE.FOPEN('MYPROCBAKPATH' , rec.ofile,'w',32767);
37   --for robj in (select owner,object_name from user_objects where object_type=rec.otype) loop --使用当前用户的
38     for robj in (select owner,object_name from dba_objects /*user_objects*/ where object_type=rec.otype and owner=towner) loop
39         --select dbms_metadata.get_ddl(rec.otype,robj.object_name,towner) into v_sql from dual;
40         UTL_FILE.put_line(OutFile,'-----------------start '||robj.object_name||')----------------');
41         pos:=1;
42         line:=1;
43         --大对象写入文件
44         UTL_FILE.put(OutFile,'create or replace ');
45     if(rec.otype='VIEW')then
46           UTL_FILE.put(OutFile,'view '||robj.object_name||chr(10));
47           execute immediate 'select text from user_views where view_name='''||robj.object_name||'''' bulk collect into user_source_table;
48     elsif(rec.otype='SEQUENCE')then
49           execute immediate 'select ''create sequnence ''||sequence_name||'' min_value ''||min_value||'' max_value ''||max_value||'' increment by ''||increment_by||'' start with ''||last_number||(case when cycle_flag=''Y'' then '' cycle '' else '''' end)||(case when order_flag=''Y'' then '' oreder '' else '''' end)||(case when cache_size>0 then '' cache ''||cache_size else '' nocache'' end) from user_sequences where SEQUENCE_NAME='''||robj.object_name||'''' bulk collect into user_source_table;
50     else
51           execute immediate 'SELECT TEXT FROM user_SOURCE WHERE name='''||robj.object_name||''' order by line ' bulk collect into user_source_table;
52     end if;
53         WHILE pos<=user_source_table.count LOOP
54       if(line>500)then --防止 文件写入缓存满了
55               dbms_output.put_line(pos||'  '||robj.object_name);
56                 UTL_FILE.fflush(OutFile);
57                 line:=1;
58             end if;
59             UTL_FILE.put(OutFile,user_source_table(pos));
60       pos:=pos+1;
61             line:=line+1;
62         END LOOP;
63     UTL_FILE.put_line(OutFile,chr(10)||'/');
64         UTL_FILE.put_line(OutFile,'-----------------end   '||robj.object_name||' (line:'||user_source_table.count||'----------------');
65     pos:=0;
66     end loop;
67   UTL_FILE.put_line(OutFile,'-----------------end of file  '||rec.ofile||'----------------',true);
68     UTL_FILE.FCLOSE(OutFile);
69 
70 end loop;
71 
72 EXCEPTION
73   WHEN OTHERS THEN
74      UTL_FILE.put(OutFile,' pos:'||pos||chr(10)||SQLERRM||chr(10)||dbms_utility.format_error_backtrace);
75      UTL_FILE.FCLOSE(OutFile);
76      dbms_output.put_line(SQLERRM||chr(10)||dbms_utility.format_error_backtrace);
77 END;

 

job执行后会在 建立的文件夹里生成备份文件

 

posted @ 2016-09-01 13:09  _Ong  阅读(1376)  评论(0)  编辑  收藏  举报