如何生成 Oracle 模式的整个 DDL(可编写脚本)?

2021-12-05 00:00:00 schema oracle ddl

谁能告诉我如何为 Oracle 模式中的所有表、视图、索引、包、过程、函数、触发器、类型、序列、同义词、授权等生成 DDL?理想情况下，我也想复制行，但这不太重要.

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.

我想在某种预定作业上执行此操作，而不是每次都手动执行，因此排除使用 SQL Developer 中的向导.

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.

理想情况下，由于我将在几个相互授予和同义词的模式上运行它，我希望有一种方法可以在输出中进行查找/替换，以便模式名称与我的新名称匹配模式将会是.

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.

谢谢！

推荐答案

您可以通过 SQL*Plus 和 dbms_metadata 包将模式假脱机到一个文件中.然后通过 sed 将架构名称替换为另一个架构名称.这适用于 Oracle 10 及更高版本.

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.

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

将所有内容放在脚本中并通过 cron(调度程序)运行它.使用高级功能时，导出对象可能会很棘手.如果您需要向上述代码添加更多例外，请不要感到惊讶.

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.

相关文章