Oracle Backup Database object (stored procedure Procedure,function,view,trigger ...)

In the development process, the need to constantly back up database objects, especially stored procedures, each time the manual backup is very low-energy AH

After several modifications finally, perfect, O (∩_∩) o haha ~ (of course, you can also change a little bit easier ~ ~ ~)

Select Dbms_metadata.get_ddl (' PROCEDURE ', ' procedure_name ', ' NAG ') encounter large stored procedures always throw things and never say, the object name is always "user name." Object name "format, bored people!~

Create OR REPLACE PROCEDURE objautostoreas/* Features: Backup stored procedures and view preparation:--1. Creating a folder: ' D:/oraclebackup/procback ';-- The path of the text store--2. Execute: Create or replace directory Myprocbakpath as ' D:/oraclebackup/procback ';--3. Assign permissions: Sqlplus/nologconn nag/ Nag as Sysdbagrant Select on Dba_objects to nag;--4. Create job, automate, automatically back up stored procedures + reduced code, collation loop execution Lzpong 2015/03/18+ change code, so that very large stored procedures can also be saved, "User name" does not appear. " The format of the object name 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 (): = ' 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) to 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| | ') ----------------');p os:=1;line:=1;--large object written to file Utl_file.put (OutFile, ' Create or replace ');  While Pos<=user_source_table.count LOOP if (line>500) then--Prevents the file write cache from being full 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;

Well, it's a lot easier.

The following continues with Oracle's automatic full-Library export script, with packaging compression OH:

:: Backup folder path and WinRAR path do not need quotation marks echo off:: filename prefix set pnm=nag_back_:: Backup folder path set Pth=d:\oraclebackup::winrar path set Rth=c:\progra M Files\winrar:: Auto Detect/Create backup folder if not exist "%pth%" (md "%pth%") echo ****************%date%, data backup plan **************** >& Gt;%pth%\%pnm%explog.logset Pth=%pth%\%pnm%echo%time%, handling old backup files >>%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%, start Backup Database >> "%pth%explog.log":: Export parameters exp Username/password owner=username file= "%pth%0.dmp" log= "%pth%0.log" if exist "%rth%\rar" Do (echo%time%, start compressing backup files >> "%pth%explog.log" "%rth%\rar" A-DF "%pth%0.rar" "%pth %0.dmp "%pth%0.log") echo%time%, complete the database backup >> "%pth%explog.log "Echo. >> "%pth%explog.log"

　　

Oracle Backup Database object (stored procedure Procedure,function,view,trigger ...)