实用的备份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
