General settings
Reset all settings:
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT');
end;
/
If the extracted SQL statement is somehow transferred into an SQL script (which might be run by
SQL*Plus, the following command causes the
SQL terminator, the semicolon, to be appended to each statement that is produced by
dbms_metadata:
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true );
end;
/
Generate create table statements
We don't want foreign key constraints to be in the create table statement, so that we don't have to care about the order in which we create the tables when the script is replayed. Unfortunately, this setting turns off all contraint clauses, even not null for columns.
There seems to be a NULL_CONSTRAINTS parameter, but setting it to either true or false does not have an influence as far as I can tell.
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'CONSTRAINTS' , false );
end;
/
Depending on the requirements, the name of the table owner might or might not be desired:
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'EMIT_SCHEMA' , false );
end;
/
Setting the parameter SEGMENT_ATTRIBUTES to false causes clauses like SEGMENT CREATION IMMEDIATE, PCTFREE … PCTUSED …, BUFFER_POOL DEFAULT or TABLESPACE to be removed from the create table statement, thus resulting in a more readable statement (which might or might not be desired):
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
end;
/
Select the create table statement:
select
dbms_metadata.get_ddl('TABLE', tab.table_name, tab.owner)
from
dba_tables tab
where
tab.owner = 'RENE';
;
Generate primary key statements
After creating the tables, we also extract the
primary key constraints. They need to be replayed after the create table statements are run.
Because it's possible that
unique key constraints might be used as foreign key targets, unique key constraints might be extracted as well:
select
dbms_metadata.get_ddl('CONSTRAINT', pk.constraint_name, pk.owner) primary_key_constraints
from
dba_constraints pk
where
pk.constraint_type in ('P' /*, 'U' */) and
pk.owner = 'RENE'
;
Generate foreign key constraints
With the primary key constraints in place, the foreign key constraints can be created.
select
dbms_metadata.get_ddl('REF_CONSTRAINT', fk.constraint_name, fk.owner) foreign_key_constraints
from
dba_constraints fk
where
fk.constraint_type = 'R' and
fk.owner = 'RENE'
;
Generate remaining constraints
Generate the remaning check and unique constraints:
select
dbms_metadata.get_ddl('CONSTRAINT', rc.constraint_name, rc.owner) remaining_constraints
from
dba_constraints rc
where
rc.constraint_type in ('C', 'U') and
rc.owner = 'RENE'
;
Generate create index statements
Generate the create index statements except for indexes of primary keys:
select
dbms_metadata.get_ddl('INDEX', ix.index_name, ix.owner) index_
from
dba_indexes ix left join
dba_constraints np on ix.index_name = np.index_name and
ix.owner = np.index_owner
where
ix.owner = 'RENE' and (
np.constraint_type is null or
np.constraint_type != 'P'
)
;
Copy views
Generate the create view statements
select
dbms_metadata.get_ddl('VIEW', vw.view_name, vw.owner) index_
from
dba_views vw
where
-- vw.view_name = '...' and
vw.owner = 'RENE'
;