如何使用DBMS_METADATA(和SCHEMA_EXPORT)将Oracle SCHEMA作为DDL脚本获取 [英] How do I get an Oracle SCHEMA as DDL scripts with DBMS_METADATA (and SCHEMA_EXPORT)

问题描述

我很难用DBMS_METADATA为给定的模式提取DDL，这可能是因为我对它的理解是错误的.

I am having troubles to extract the DDL for a given schema with DBMS_METADATA, probably because my understanding of it is wrong.

这基本上是我要做的:

set termout off

create table copy_dml_schema(c clob, i number);

declare

  m    number;
  t    number;
  e    number;
  c    clob;
  i    number := 0;

begin

  e   :=  dbms_metadata.session_transform;


  dbms_metadata.set_transform_param   (e, 'REF_CONSTRAINTS'     ,  false   );
  dbms_metadata.set_transform_param   (e, 'CONSTRAINTS_AS_ALTER',  true    );
  dbms_metadata.set_transform_param   (e, 'CONSTRAINTS'         ,  true    );
  dbms_metadata.set_transform_param   (e, 'FORCE'               ,  true    );


  m   :=  dbms_metadata.open('SCHEMA_EXPORT');
  t   :=  dbms_metadata.add_transform (m, 'DDL'                     );

  dbms_metadata.set_transform_param   (t, 'PRETTY'              ,  true    );
  dbms_metadata.set_transform_param   (t, 'SQLTERMINATOR'       ,  true    );

  dbms_metadata.set_filter            (m, 'SCHEMA'              , 'XYZ');
  dbms_metadata.set_filter            (m, 'EXCLUDE_PATH_EXPR'   , 'in ('   ||
                                            '''GRANT''          ,' || 
                                            '''SYNONYM''        ,' || 
                                            '''STATISTICS''     ,' || 
                                            '''COMMENT''         ' ||
                                            ')');


  loop
    c   :=  dbms_metadata.fetch_clob(m);
    exit when c is null;
    insert into copy_dml_schema values (c, i);
    i := i+1;
  end loop;

  dbms_metadata.close(m);

end;
/

commit;


set pages     0
set trimspool on
set long      1000000
set lines         300
set longchunksize 300


spool c:\temp\the_schema.sql

select 
  c 
from 
  copy_dml_schema 
order 
  by i; 

spool off

drop table copy_dml_schema;

set termout on

给我的印象是，此方法将以可以创建它们的顺序返回"CREATE TABLE"语句，也就是说，以后将发出依赖表.

I was under the impression that this method would return the "CREATE TABLE" statements in such order that they could be created, that is, dependent tables would be emitted later.

但是，事实证明，表的顺序是任意的，因为某些表是在带有外键约束的情况下发出的，该外键约束引用了尚未发出的表.

It turns out, however, that the order of the tables is arbitrary in that some tables are emitted with a foreign key constraint that references a table that has not been emitted.

为了解决"此问题，我分别将REF_CONSTRAINT和CONSTRAINTS_AS_ALTER设置为false和true，因为我认为这会使我的问题消失.情况并非如此.

In order to "solve" this problem, I set the the REF_CONSTRAINT and CONSTRAINTS_AS_ALTER to false and true, respectively, because I assumed this would make my problem go away. Which is not the case.

那么，是否可以解决我的问题，还是有我忽略的设置?

So, is there a work around to my problem, or is there a setting I overlooked?

推荐答案

与其说是观察，不如说是答案. 从技术上讲，可以在约束中使用循环引用(但实际上可能很愚蠢).

Not so much an answer as an observation. It is technically possible (but probably daft in practice) to have circular references in constraints.

create table blue (blue_id number primary key, val varchar2(10), red_id number);
create table red (red_id number primary key, val varchar2(10), blue_id number);

insert into blue values (1,'test',2);
insert into red values (2,'test',1);

alter table blue add constraint blue_fk foreign key (red_id) references red (red_id);
alter table red add constraint red_fk foreign key (blue_id) references blue (blue_id);

所以我能理解他们是否决定，因为不一定总是可以实现的，所以他们不会费心将对象置于依赖关系顺序中.

So I could understand if they decided that, because it is not necessarily always achievable, they wouldn't bother putting the objects in dependency order.

这样，我将在创建表时省去引用约束，然后在创建所有表后将它们作为ALTER来应用.

As such, I'd leave the referential constraints out when tables are being created, then apply them as ALTERs after all the tables have been created.

这篇关于如何使用DBMS_METADATA(和SCHEMA_EXPORT)将Oracle SCHEMA作为DDL脚本获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！