SPOOL spool/procedure.sql
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name)
FROM user_objects u WHERE object_type='PROCEDURE';
SPOOL OFF;
获取某个SCHEMA的建全部函数的语法：
SET PAGESIZE 1000
SET LINESIZE 120
SET NEWPAGE 0
SET ECHO OFF
SET LONG 10000
SPOOL spool/function.sql
SELECT DBMS_METADATA.GET_DDL('FUNCTION',u.object_name)
FROM user_objects u WHERE object_type='FUNCTION';
SPOOL OFF;
---------------------------------------------------------------------------
执行DBMS_METADATA.GET_DDL报ORA-39212的解决方法
以sys登陆，执行DBMS_METADATA.get_ddl得到某个表空间的元数据报错
select DBMS_METADATA.get_ddl('TABLESPACE','PDA') from dual;
ERROR:
ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7428
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7477
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9495
ORA-06512: at "SYS.DBMS_METADATA", line 1920
ORA-06512: at "SYS.DBMS_METADATA", line 2793
.........
解决方法：
以sysdba权限的用户登陆执行以下语句：
SQL> exec dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.
再次执行成功：
SQL> selectDBMS_METADATA.get_ddl('TABLESPACE','PDA') from dual;