oracle 備份資料庫物件(預存程序PROCEDURE,FUNCTION,VIEW,TRIGGER...)

標籤：

開發過程中,需要不停的備份資料庫物件, 特別是預存程序, 每次手動備份不免很低能啊

曆經幾次修改終於, 完美了,O(∩_∩)O哈哈~      (當然,你也可以再改簡便一點~~~)

select dbms_metadata.get_ddl(‘PROCEDURE‘,"PROCEDURE_NAME",‘NAG‘) 遇到大預存程序老是丟東西不說, 對象名 還老是 "使用者名稱"."對象名" 的格式,膩煩人!~

CREATE OR REPLACE PROCEDURE OBJAUTOSTOREAS/* 功能：備份預存程序和視圖準備工作：--1.建立檔案夾 ：‘D:/OracleBackUp/ProcBack‘;--文本存放的路徑--2.執行：create or replace directory MyProcBakPath as ‘D:/OracleBackUp/ProcBack‘;--3.賦許可權：sqlplus /nologconn nag/nag as sysdbagrant 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 isSELECT ‘PROCEDURE‘ otype,‘Proc_‘||to_char(sysdate, ‘yyyymmddhh24miss‘)||‘.sql‘ ofile FROM DUALunion all SELECT ‘VIEW‘ otab,‘View_‘||to_char(sysdate, ‘yyyymmddhh24miss‘)||‘.sql‘ ofile FROM DUALunion all SELECT ‘TRIGGER‘ otab, ‘Trig_‘||to_char(sysdate, ‘yyyymmddhh24miss‘)||‘.sql‘ ofile FROM DUALunion all SELECT ‘SEQUENCE‘ otab, ‘Sequ_‘||to_char(sysdate, ‘yyyymmddhh24miss‘)||‘.sql‘ ofile FROM DUALunion all SELECT ‘FUNCTION‘ otab, ‘Func_‘||to_char(sysdate, ‘yyyymmddhh24miss‘)||‘.sql‘ ofile FROM DUAL;BEGINfor rec in abc loopdbms_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;

　好了, 輕鬆了不少了吧~~　

 

下面繼續奉上 Oracle的自動全庫匯出指令碼,還帶打包壓縮哦:

::備份檔案夾 路徑 和 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.logset pth=%pth%\%pnm%echo %time%,處理老的備份檔案 >>%pth%explog.logif exist "%rth%\rar" do (del "%pth%6.rar"ren "%pth%5.rar" %pnm%6.rarren "%pth%4.rar" %pnm%5.rarren "%pth%3.rar" %pnm%4.rarren "%pth%2.rar" %pnm%3.rarren "%pth%1.rar" %pnm%2.rarren "%pth%0.rar" %pnm%1.rar) else (del "%pth%6.dmp"ren "%pth%5.dmp" %pnm%6.dmpren "%pth%4.dmp" %pnm%5.dmpren "%pth%3.dmp" %pnm%4.dmpren "%pth%2.dmp" %pnm%3.dmpren "%pth%1.dmp" %pnm%2.dmpren "%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%,完成Database Backup >>"%pth%explog.log"echo. >>"%pth%explog.log"

　　

oracle 備份資料庫物件(預存程序PROCEDURE,FUNCTION,VIEW,TRIGGER...)