如何生成 Oracle 模式的整个 DDL（可编写脚本）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10886450/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to generate entire DDL of an Oracle schema (scriptable)?
提问by Eric
Can anyone tell me how I can generate the DDL for all tables, views, indexes, packages, procedures, functions, triggers, types, sequences, synonyms, grants, etc. inside an Oracle schema? Ideally, I would like to copy the rows too but that is less important.
谁能告诉我如何为 Oracle 模式中的所有表、视图、索引、包、过程、函数、触发器、类型、序列、同义词、授权等生成 DDL？理想情况下，我也想复制行，但这不太重要。
I want to do this on a scheduled job of some kind and not manually each time, so that rules out using the wizard in SQL Developer.
我想在某种预定的作业上执行此操作，而不是每次都手动执行，因此排除在 SQL Developer 中使用向导的可能性。
Ideally, since I will be running this on several schemas that have grants and synonyms to one another, I would like to have a way to do a find/replace in the output so the schema names match whatever the names of my new schemas are going to be.
理想情况下，由于我将在几个相互授予和同义词的模式上运行它，我希望有一种方法可以在输出中进行查找/替换，以便模式名称与我的新模式的名称相匹配成为。
Thanks!
谢谢！
回答by MichaelN
You can spool the schema out to a file via SQL*Plus and dbms_metadata package. Then replace the schema name with another one via sed. This works for Oracle 10 and higher.
您可以通过 SQL*Plus 和 dbms_metadata 包将模式假脱机到一个文件中。然后通过 sed 将模式名称替换为另一个名称。这适用于 Oracle 10 及更高版本。
sqlplus<<EOF
set long 100000
set head off
set echo off
set pagesize 0
set verify off
set feedback off
spool schema.out
select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
--Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
select
owner,
--Java object names may need to be converted with DBMS_JAVA.LONGNAME.
--That code is not included since many database don't have Java installed.
object_name,
decode(object_type,
'DATABASE LINK', 'DB_LINK',
'JOB', 'PROCOBJ',
'RULE SET', 'PROCOBJ',
'RULE', 'PROCOBJ',
'EVALUATION CONTEXT', 'PROCOBJ',
'CREDENTIAL', 'PROCOBJ',
'CHAIN', 'PROCOBJ',
'PROGRAM', 'PROCOBJ',
'PACKAGE', 'PACKAGE_SPEC',
'PACKAGE BODY', 'PACKAGE_BODY',
'TYPE', 'TYPE_SPEC',
'TYPE BODY', 'TYPE_BODY',
'MATERIALIZED VIEW', 'MATERIALIZED_VIEW',
'QUEUE', 'AQ_QUEUE',
'JAVA CLASS', 'JAVA_CLASS',
'JAVA TYPE', 'JAVA_TYPE',
'JAVA SOURCE', 'JAVA_SOURCE',
'JAVA RESOURCE', 'JAVA_RESOURCE',
'XML SCHEMA', 'XMLSCHEMA',
object_type
) object_type
from dba_objects
where owner in ('OWNER1')
--These objects are included with other object types.
and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
--Ignore system-generated types that support collection processing.
and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
--Exclude nested tables, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
--Exclude overflow segments, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
order by owner, object_type, object_name;
spool off
quit
EOF
cat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql
Put everything in a script and run it via cron (scheduler). Exporting objects can be tricky when advanced features are used. Don't be surprised if you need to add some more exceptions to the above code.
将所有内容放在脚本中并通过 cron（调度程序）运行它。使用高级功能时，导出对象可能会很棘手。如果您需要向上述代码添加更多例外，请不要感到惊讶。
回答by Gurupreet Singh Bhatia
If you want to individually generate ddl for each object,
如果要为每个对象单独生成 ddl，
Queries are:
查询是：
--GENERATE DDL FOR ALL USER OBJECTS
--为所有用户对象生成 DDL
--1. FOR ALL TABLES
--1. 适用于所有表
SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;
--2. FOR ALL INDEXES
--2. 对于所有索引
SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';
--3. FOR ALL VIEWS
--3. 适用于所有视图
SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME) FROM USER_VIEWS;
OR
或者
SELECT TEXT FROM USER_VIEWS
--4. FOR ALL MATERILIZED VIEWS
--4. 对于所有物化视图
SELECT QUERY FROM USER_MVIEWS
--5. FOR ALL FUNCTION
--5. 适用于所有功能
SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'
===============================================================================================
================================================== ==============================================
GET_DDLFunction doesnt support for some object_type like LOB,MATERIALIZED VIEW, TABLE PARTITION
GET_DDL函数不支持某些 object_type，如 LOB、MATERIALIZED VIEW、TABLE PARTITION
SO, Consolidated query for generating DDL will be:
因此，用于生成 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;
回答by Vitor Pinto
The get_ddl procedure for a PACKAGE will return both spec AND body, so it will be better to change the query on the all_objects so the package bodies are not returned on the select.
PACKAGE 的 get_ddl 过程将同时返回规范和主体，因此最好更改 all_objects 上的查询，以便选择时不返回包主体。
So far I changed the query to this:
到目前为止，我将查询更改为：
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;
Although other changes might be needed depending on the object types you are getting...
尽管可能需要根据您获得的对象类型进行其他更改...
回答by v0devil
There is a problem with objects such as PACKAGE_BODY:
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');
回答by ravi
First export the schema metadata:
首先导出架构元数据：
expdp dumpfile=filename logfile=logname directory=dir_name schemas=schema_name
and then import by using the sqlfileoption (it will not import data it will just write the schema DDL to that file)
然后使用该sqlfile选项导入（它不会导入数据，它只会将架构 DDL 写入该文件）
impdp dumpfile=filename logfile=logname directory=dir_name sqlfile=ddl.sql
回答by Lalit Kumar B
To generate the DDL script for an entire SCHEMAi.e. a USER, you could use dbms_metadata.get_ddl.
要为整个SCHEMA即USER生成 DDL 脚本，您可以使用dbms_metadata.get_ddl。
Execute the following script in SQL*Pluscreated by Tim Hall:
在Tim Hall创建的SQL*Plus 中执行以下脚本：
Provide the usernamewhen prompted.
出现提示时提供用户名。
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
回答by user3520310
The output of this query is very clean (original here)
这个查询的输出非常干净（原文在这里）
clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt ' Output filename : '
spool &&outfile..gen
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;
spool off
