ORACLE导出某用户下PROCEDURE等脚本的可靠方法

spool e:\sql\20110402_demo_back_script_PROCEDURE.sql
select text from all_source WHERE OWNER='DEMO' and TYPE='PROCEDURE';
spool off
spool e:\sql\20110402_demo_back_script_FUNCTION.sql
select text from all_source WHERE OWNER='DEMO' and TYPE='FUNCTION';
spool off
spool e:\sql\20110402_demo_back_script_PACKAGE.sql
select text from all_source WHERE OWNER='DEMO' and TYPE='PACKAGE';
spool off
spool e:\sql\20110402_demo_back_script_PACKAGE_BODY.sql
select text from all_source WHERE OWNER='DEMO' and TYPE='PACKAGE BODY';
spool off
spool e:\sql\20110402_demo_back_script_VIEW.sql
select text from all_source WHERE OWNER='DEMO' and TYPE='VIEW';
spool off

注意没有TYPE为TABLE的数据
spool e:\sql\20110402_demo_back_script_TABLE.sql
select text from all_source WHERE OWNER='DEMO' and TYPE='TABLE';
spool off

create table tmp_save_tableddl(table_name varchar(100),table_sql clob);
insert into  tmp_save_tableddl SELECT table_name,DBMS_METADATA.GET_DDL('TABLE',table_name) as table_sql FROM USER_TABLES;
commit;
create table tmp_save_indexddl(index_name varchar(100),index_sql clob);
insert into  tmp_save_indexddl SELECT index_name,DBMS_METADATA.GET_DDL('INDEX',index_name) as index_sql FROM USER_INDEXES;
commit;

本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/534153，如需转载请自行联系原作者