實用的備份PL/SQL程序工具
2024-07-21 02:08:33
供稿：網友
/*[email protected]*/
功能: 用于備份當前用戶所擁有的所有pl/sql objects (包括
type,type body, procedure , function, package, package body or java source )
原理: 對user_source數據字典的調用,得到所有的
pl/sql 代碼.
使用方法舉例:
1. 在c盤建立目錄c:/export
2. 將export_source.sql和extract_source.sql拷貝到c盤根目錄.
3. 登陸sqlplus , connect scott/tiger
4. 運行@c:/export_source.sql
5. 執行結束,所有的scott擁有的pl/sql object的代碼文件建立在c:/export目錄里．
后綴名.pks 表示package
后綴名.pkb 表示package body
后綴名.sql 表示其他objects
備注：
如果想得到數據庫中各個schema的pl/sql objects ,只需把工具代碼中的user_source改成dba_source,由system
運行即可.
工具代碼：
export_source.sql
set serveroutput on size 1000000
set echo off verify off feedback off trimspool on pages 0 lines 512
set termout off
set termout on
prompt
prompt pl/sql export utility
prompt
prompt this utilty exports all of the current schema's pl/sql source code into
prompt a subdirectory called export.
prompt
prompt exporting current user's source to folder ./export
set termout off
spool temp_source_extract.sql
prompt set echo off verify off feedback off trimspool on termout off pages 0 lines 512
declare
/*
|| this cursor extracts each pl/sql stored procedure's name and procedure type
*/
cursor cur_source_programs
is
select distinct us.name, us.type,
us.name || decode(us.type, 'package', '.pks',
'package body', '.pkb',
'.sql') spool_file
from user_source us
order by us.name, us.type;
begin
for cur_source_programs_row in cur_source_programs
loop
dbms_output.put_line('spool export/' || user || '_' || cur_source_programs_row.spool_file);
dbms_output.put_line('@extract_source ' || cur_source_programs_row.name || ' "' || cur_source_programs_row.type || '"');
dbms_output.put_line('spool off');
end loop;
end;
/
spool off
@temp_source_extract
set feedback on verify on termout on
prompt export complete!
prompt
extract_source.sql:
set head off verify off
prompt --************************************************************************************--;
prompt --*;
prompt --* script: &2 &1;
prompt --*;
prompt --* author:;
prompt --*;
prompt --*;
prompt --* purpose:;
prompt --*;
prompt --*;
prompt --*;
prompt --*;
prompt --*;
prompt --* parameters:;
prompt --*;
prompt --*;
prompt --* dependencies: none;
prompt --*;
prompt --* revisions:;
prompt --* ver date author description;
prompt --* --------- ---------- ------------------ ------------------------------------;
prompt --* ;
prompt --*;
prompt --*************************************************************************************--;
select decode(rownum, 1, 'create or replace '|| rtrim(rtrim(us.text, chr(10) )),
rtrim(rtrim(us.text, chr(10) ))) text
from user_source us
where us.name = '&1'
and us.type = '&2'
order by us.line;
prompt /
prompt
