例如存储过程可以这样导出C:\>sqlplus user/password@dbSQL*Plus: Release 9.2.0.1.0 - Production on 星期二 3月 16 10:17:58 2010Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining optionsSQL> set linesize 1000 SQL> set pagesize 0 SQL> set echo off SQL> set feedback off SQL> set heading off SQL> set termout off SQL> set trimspool on SQL> spool test.sql SQL> select text from user_source order by name,line; SQL> spool off SQL> 这样就可以把存储过程都导出到test.sql中 其他的对象找到相应的系统视图进行组装就好了
1、可以使用pl/sql developer的导出功能; 2、使用exp逻辑备份功能; 3、直接查询字典表并导出;SELECT text FROM user_source ORDER BY NAME, line;
命令行 好像 费劲。 建议工具导吧 dbms_metadata.get_ddl
spool /u01/getddl.txtdeclare v_sql clob; cursor aaa is select owner,object_name from dba_objects where object_type='PROCEDURE' and owner='OPER'; begin for rec in aaa loop select dbms_metadata.get_ddl('PROCEDURE',rec.object_name,'OPER') into v_sql from dual; dbms_output.put_line(v_sql); dbms_output.put_line('---------------------------------'); end loop; end; /spool off可以修改这个过程,做成传参数的类型,然后传入用户名,对象类型,比如过程、函数、触发器等等。
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining optionsSQL> set linesize 1000
SQL> set pagesize 0
SQL> set echo off
SQL> set feedback off
SQL> set heading off
SQL> set termout off
SQL> set trimspool on
SQL> spool test.sql
SQL> select text from user_source order by name,line;
SQL> spool off
SQL>
这样就可以把存储过程都导出到test.sql中
其他的对象找到相应的系统视图进行组装就好了
2、使用exp逻辑备份功能;
3、直接查询字典表并导出;SELECT text FROM user_source ORDER BY NAME, line;
建议工具导吧
dbms_metadata.get_ddl
v_sql clob;
cursor aaa is select owner,object_name
from dba_objects
where object_type='PROCEDURE'
and owner='OPER';
begin
for rec in aaa loop
select dbms_metadata.get_ddl('PROCEDURE',rec.object_name,'OPER')
into v_sql from dual;
dbms_output.put_line(v_sql);
dbms_output.put_line('---------------------------------');
end loop;
end;
/spool off可以修改这个过程,做成传参数的类型,然后传入用户名,对象类型,比如过程、函数、触发器等等。