CREATE OR REPLACE PROCEDURE OBJAUTOSTORE

AS

/* 功能：备份存储过程和视图

准备工作：

--1.创建文件夹 ：'D:/OracleBackUp/ProcBack';--文本存放的路径

--2.执行：create or replace directory MyProcBakPath as 'D:/OracleBackUp/ProcBack';

--3.赋权限：

sqlplus /nolog

conn nag/nag as sysdba

grant select on DBA_OBJECTS to NAG;

--4.创建Job，自动执行，自动备份存储过程

+缩减代码,归类循环执行 lzpong 2015/03/18

+更改代码,使超大存储过程也能保存,并且 不会出现 "用户名"."对象名" 的格式  lzpong 2015/07/14

*/

OutFile   UTL_FILE.FILE_TYPE;

type user_source_table_type is table of user_source.text%TYPE INDEX BY BINARY_INTEGER;

user_source_table user_source_table_type;

pos INTEGER;

line integer;

towner VARCHAR2(50) :='NAG';

cursor abc is

SELECT 'PROCEDURE' otype,'Proc_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql' ofile FROM DUAL

union all SELECT 'VIEW' otab,'View_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql' ofile FROM DUAL

union all SELECT 'TRIGGER' otab, 'Trig_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql' ofile FROM DUAL

union all SELECT 'SEQUENCE' otab, 'Sequ_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql' ofile FROM DUAL

union all SELECT 'FUNCTION' otab, 'Func_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql' ofile FROM DUAL

;

BEGIN

for rec in abc loop

dbms_output.put_line(rec.otype||','||rec.ofile);

  OutFile:=UTL_FILE.FOPEN('MYPROCBAKPATH' , rec.ofile,'w',32767);

	for robj in (select owner,object_name from dba_objects where object_type=rec.otype and owner=towner) loop

		--select dbms_metadata.get_ddl(rec.otype,robj.object_name,towner) into v_sql from dual;

		execute immediate 'SELECT TEXT FROM user_SOURCE WHERE name='''||robj.object_name||''' order by line ' bulk collect into user_source_table;

		UTL_FILE.put_line(OutFile,'-----------------start '||robj.object_name||' (line:'||user_source_table.count||')----------------');

		pos:=1;

		line:=1;

		--大对象写入文件

		UTL_FILE.put(OutFile,'create or replace ');

		WHILE pos<=user_source_table.count LOOP

      if(line>500)then --防止 文件写入缓存满了

			  dbms_output.put_line(pos||'  '||robj.object_name);

				UTL_FILE.fflush(OutFile);

				line:=1;

			end if;

			UTL_FILE.put(OutFile,user_source_table(pos));

      pos:=pos+1;

			line:=line+1;

		END LOOP;

		UTL_FILE.put_line(OutFile,'-----------------end   '||robj.object_name||'----------------');

	end loop;

  UTL_FILE.put_line(OutFile,'-----------------end of file  '||rec.ofile||'----------------',true);

	UTL_FILE.FCLOSE(OutFile);

end loop;

EXCEPTION

  WHEN OTHERS THEN

     UTL_FILE.put(OutFile,' pos:'||pos||chr(10)||SQLERRM||chr(10)||dbms_utility.format_error_backtrace);

     UTL_FILE.FCLOSE(OutFile);

     dbms_output.put_line(SQLERRM||chr(10)||dbms_utility.format_error_backtrace);

END;

::备份文件夹 路径 和 WinRAR 路径 不需要引号

echo off

::文件名前缀

set pnm=NAG_Back_

::备份文件夹 路径

set pth=D:\OracleBackUp

::WinRAR 路径

set rth=C:\Program Files\WinRaR

::自动检测/创建备份文件夹

if not exist "%pth%" ( md "%pth%" )

echo ****************%date%,数据备份计划**************** >>%pth%\%pnm%explog.log

set pth=%pth%\%pnm%

echo %time%,处理老的备份文件 >>%pth%explog.log

if exist "%rth%\rar" do (

del "%pth%6.rar"

ren "%pth%5.rar" %pnm%6.rar

ren "%pth%4.rar" %pnm%5.rar

ren "%pth%3.rar" %pnm%4.rar

ren "%pth%2.rar" %pnm%3.rar

ren "%pth%1.rar" %pnm%2.rar

ren "%pth%0.rar" %pnm%1.rar

) else (

del "%pth%6.dmp"

ren "%pth%5.dmp" %pnm%6.dmp

ren "%pth%4.dmp" %pnm%5.dmp

ren "%pth%3.dmp" %pnm%4.dmp

ren "%pth%2.dmp" %pnm%3.dmp

ren "%pth%1.dmp" %pnm%2.dmp

ren "%pth%0.dmp" %pnm%1.dmp

)

echo %time%,开始备份数据库 >>"%pth%explog.log"

::导出参数

exp username/password owner=username file="%pth%0.dmp" log="%pth%0.log"

if exist "%rth%\rar" do (

echo %time%,开始压缩备份文件 >>"%pth%explog.log"

"%rth%\rar" a -df "%pth%0.rar" "%pth%0.dmp" "%pth%0.log"

)

echo %time%,完成数据库备份 >>"%pth%explog.log"

echo. >>"%pth%explog.log"