oracle生成ddl语句,如何生成一个Oracle模式的完整DDL（可编写脚本）？

您可以通过SQL * Plus和dbms_metadata软件包将模式转换为文件。 然后通过sedreplace另一个模式名称。 这适用于Oracle 10及更高版本。

sqlplus<schema.out.change.sql

把所有东西放在脚本中，并通过cron(scheduler)运行。 使用高级function时，导出对象可能会非常棘手。 如果您需要在上面的代码中添加更多的例外，请不要惊讶。

PACKAGE_BODY等对象存在问题：

SELECT DBMS_METADATA.get_ddl(object_Type, object_name, owner) FROM ALL_OBJECTS WHERE OWNER = 'WEBSERVICE'; ORA-31600 invalid input value PACKAGE BODY parameter OBJECT_TYPE in function GET_DDL ORA-06512: на "SYS.DBMS_METADATA", line 4018 ORA-06512: на "SYS.DBMS_METADATA", line 5843 ORA-06512: на line 1 31600. 00000 - "invalid input value %s for parameter %s in function %s" *Cause: A NULL or invalid value was supplied for the parameter. *Action: Correct the input value and try the call again. SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type,' ','_'), object_name, owner) FROM all_OBJECTS WHERE (OWNER = 'OWNER1');

一个PACKAGE的get_ddl过程将同时返回spec和body，所以最好在all_objects上改变查询，这样包体不会在select上返回。

到目前为止，我改变了这个查询：

SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) FROM all_OBJECTS WHERE (OWNER = 'OWNER1') and object_type not like '%PARTITION' and object_type not like '%BODY' order by object_type, object_name;

虽然可能需要根据所获得的对象types进行其他更改…

如果你想单独为每个对象生成ddl，

查询是：

– 为所有用户对象生成DDL

–1。 适用于所有表格

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;

–2。 适用于所有指标

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

–3。 为所有视图

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME) FROM USER_VIEWS;

要么

SELECT TEXT FROM USER_VIEWS

–4。 所有的材料化视图

SELECT QUERY FROM USER_MVIEWS

–5。 为所有function

SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'

================================================== =============================================

GET_DDL函数不支持像LOB，MATERIALIZED VIEW，TABLE PARTITION这样的object_type

SO，生成DDL的合并查询将是：

SELECT OBJECT_TYPE, OBJECT_NAME,DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) FROM ALL_OBJECTS WHERE (OWNER = 'XYZ') AND OBJECT_TYPE NOT IN('LOB','MATERIALIZED VIEW', 'TABLE PARTITION') ORDER BY OBJECT_TYPE, OBJECT_NAME;

首先导出架构元数据，然后通过使用sqlfile选项导入(它不会导入数据，只会将ddl架构写入该文件)

例如： – expdp dumpfile=filename logfile=logname directory=dir name schemas=schema name

username:/ as sysdba

impdp dumpfile=filename logfile=logname directory=dir sqlfile=sql.log

username:/ as sysdba

要为整个SCHEMA(即USER)生成DDL脚本，可以使用dbms_metadata.get_ddl 。

在Tim Hall创build的SQL * Plus中执行以下脚本：

提示时提供用户名 。

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on column ddl format a1000 begin dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true); dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true); end; / variable v_username VARCHAR2(30); exec:v_username := upper('&1'); select dbms_metadata.get_ddl('USER', u.username) AS ddl from dba_users u where u.username = :v_username union all select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl from dba_ts_quotas tq where tq.username = :v_username and rownum = 1 union all select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl from dba_role_privs rp where rp.grantee = :v_username and rownum = 1 union all select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl from dba_sys_privs sp where sp.grantee = :v_username and rownum = 1 union all select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl from dba_tab_privs tp where tp.grantee = :v_username and rownum = 1 union all select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl from dba_role_privs rp where rp.grantee = :v_username and rp.default_role = 'YES' and rownum = 1 union all select to_clob('/* Start profile creation script in case they are missing') AS ddl from dba_users u where u.username = :v_username and u.profile <> 'DEFAULT' and rownum = 1 union all select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = :v_username and u.profile <> 'DEFAULT' union all select to_clob('End profile creation script */') AS ddl from dba_users u where u.username = :v_username and u.profile <> 'DEFAULT' and rownum = 1 / set linesize 80 pagesize 14 feedback on trimspool on verify on