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

您可以通过sql * Plus和DBMS_R_700_11845@etadata包将该模式转储到文件。然后通过sed用另一个模式名称替换模式名称。这适用于Oracle 10和更高版本。

sqlplus<<EOF
set long 100000
set head off
set echo off
set pagesize 0
set verify off
set FeedBACk off
spool scheR_700_11845@a.out

@R_801_10288@ct DBMS_R_700_11845@etadata.get_ddl(object_type,object_name,owner)
from
(
    --Convert dba_objecTs.object_type to DBMS_R_700_11845@etaDATA object type:
    @R_801_10288@ct
        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','RulE','EVALUATION COntexT','PACKAGE','PACKAGE_SPEC','PACKAGE BODY','PACKAGE_BODY','TYPE','TYPE_SPEC','@R_946_3772@','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',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)
        --Exlclude overflow segments,table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
order by owner,object_type,object_name;

spool off
quit
EOF

cat scheR_700_11845@a.out|sed 's/owneR1/MYowneR/g'>scheR_700_11845@a.out.change.sql

把一切放在一个脚本中，并通过cron(调度程序)运行它。使用高级功能时导出对象可能很棘手。不要惊讶，如果你需要添加一些更多的例外到上面的代码。