<legend id='xZQhX'><style id='xZQhX'><dir id='xZQhX'><q id='xZQhX'></q></dir></style></legend>
<tfoot id='xZQhX'></tfoot>

<small id='xZQhX'></small><noframes id='xZQhX'>

        <bdo id='xZQhX'></bdo><ul id='xZQhX'></ul>

    1. <i id='xZQhX'><tr id='xZQhX'><dt id='xZQhX'><q id='xZQhX'><span id='xZQhX'><b id='xZQhX'><form id='xZQhX'><ins id='xZQhX'></ins><ul id='xZQhX'></ul><sub id='xZQhX'></sub></form><legend id='xZQhX'></legend><bdo id='xZQhX'><pre id='xZQhX'><center id='xZQhX'></center></pre></bdo></b><th id='xZQhX'></th></span></q></dt></tr></i><div id='xZQhX'><tfoot id='xZQhX'></tfoot><dl id='xZQhX'><fieldset id='xZQhX'></fieldset></dl></div>

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

        How to generate entire DDL of an Oracle schema (scriptable)?(如何生成 Oracle 模式的整个 DDL(可编写脚本)?)

        <tfoot id='HWMAQ'></tfoot>
          • <bdo id='HWMAQ'></bdo><ul id='HWMAQ'></ul>
              <tbody id='HWMAQ'></tbody>

            <small id='HWMAQ'></small><noframes id='HWMAQ'>

            <i id='HWMAQ'><tr id='HWMAQ'><dt id='HWMAQ'><q id='HWMAQ'><span id='HWMAQ'><b id='HWMAQ'><form id='HWMAQ'><ins id='HWMAQ'></ins><ul id='HWMAQ'></ul><sub id='HWMAQ'></sub></form><legend id='HWMAQ'></legend><bdo id='HWMAQ'><pre id='HWMAQ'><center id='HWMAQ'></center></pre></bdo></b><th id='HWMAQ'></th></span></q></dt></tr></i><div id='HWMAQ'><tfoot id='HWMAQ'></tfoot><dl id='HWMAQ'><fieldset id='HWMAQ'></fieldset></dl></div>

            1. <legend id='HWMAQ'><style id='HWMAQ'><dir id='HWMAQ'><q id='HWMAQ'></q></dir></style></legend>
                  本文介绍了如何生成 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.

                  这篇关于如何生成 Oracle 模式的整个 DDL(可编写脚本)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM(Oracle PL/SQL - 使用自定义 SQLERRM 引发用户定义的异常)
                  Oracle: is there a tool to trace queries, like Profiler for sql server?(Oracle:是否有跟踪查询的工具,例如用于 sql server 的 Profiler?)
                  SELECT INTO using Oracle(使用 Oracle SELECT INTO)
                  How to handle Day Light Saving in Oracle database(如何在 Oracle 数据库中处理夏令时)
                  PL/SQL - Use quot;Listquot; Variable in Where In Clause(PL/SQL - 使用“列表Where In 子句中的变量)
                  Oracle: Import CSV file(Oracle:导入 CSV 文件)

                    • <tfoot id='PFtPA'></tfoot>

                      <small id='PFtPA'></small><noframes id='PFtPA'>

                        <tbody id='PFtPA'></tbody>
                      1. <i id='PFtPA'><tr id='PFtPA'><dt id='PFtPA'><q id='PFtPA'><span id='PFtPA'><b id='PFtPA'><form id='PFtPA'><ins id='PFtPA'></ins><ul id='PFtPA'></ul><sub id='PFtPA'></sub></form><legend id='PFtPA'></legend><bdo id='PFtPA'><pre id='PFtPA'><center id='PFtPA'></center></pre></bdo></b><th id='PFtPA'></th></span></q></dt></tr></i><div id='PFtPA'><tfoot id='PFtPA'></tfoot><dl id='PFtPA'><fieldset id='PFtPA'></fieldset></dl></div>
                      2. <legend id='PFtPA'><style id='PFtPA'><dir id='PFtPA'><q id='PFtPA'></q></dir></style></legend>

                          • <bdo id='PFtPA'></bdo><ul id='PFtPA'></ul>