How do I get an Oracle SCHEMA as DDL scripts with DBMS_METADATA (and SCHEMA_EXPORT)

Question

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

Here's what I basically do:

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

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.

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?

Answer 1

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.

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.