Creating table definitions via OMB*Plus

Recently I had task for importing into OWB large Oracle database schema (about 4000 tables with more than 6000 primary/unique/foreign key constraints). The import process took more than 2 hours (I disabled importing physical properties, paritions and indexes). I tried another approach - generating OMB*Plus script for creating tables (with constraints)  from database dictionary. This approach works twice faster - generated OMB*Plus script created all tables withing 1 hour.

The first create this function (used for extracting CSV list of columns which compose specified constraint):
create or replace function "OWBLand_Get_Constraint_Columns" (P_TABLE_NAME IN VARCHAR2, P_CONSTRAINT_NAME IN VARCHAR2) RETURN VARCHAR2 AS
  vColumnList VARCHAR2(2000);
  TYPE tColumnTab IS TABLE OF VARCHAR2(100);
  vColumnTab tColumnTab;
  CURSOR curConstraints(pt VARCHAR2,pc VARCHAR2) IS
     select column_name from user_cons_columns where table_name=pt and constraint_name=pc order by position;
BEGIN
    OPEN curConstraints(P_TABLE_NAME, P_CONSTRAINT_NAME);
    FETCH curConstraints BULK COLLECT INTO vColumnTab;
    CLOSE curConstraints;
    FOR i in vColumnTab.FIRST .. vColumnTab.LAST LOOP
        IF i=vColumnTab.FIRST THEN vColumnList:= ''''||vColumnTab(i)||'''';
        ELSE vColumnList:=vColumnList||','''||vColumnTab(i)||''''; END IF;
    END LOOP;
    RETURN vColumnList;
END;
/


Save this script to file (for example with name gen_model.sql, don't forget to change target filename in spool command at the start of this script):

set echo off
set pages 1000
btitle off
set heading off
set feedback off

select 'set StartTime [clock seconds]' omb_command from dual;

column table_name NEW_VALUE crtab NOPRINT
column table_comment NEW_VALUE tabcomm NOPRINT
ttitle left 'OMBCREATE TABLE ''' crtab ''' SET PROPERTIES(DESCRIPTION) VALUES(''' tabcomm ''')'
break on table_name skip page

spool c:\owb_temp.tcl

select replace(tc.table_name,'$','\$') as table_name,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(tcm.comments,'\','\\'),';','\;'),'''',''''''),'"','\"'),'[','\['),']','\]'),'{','\{'),'}','\}'),'$','\$'),chr(10),'\n'),chr(9),'\t') table_comment,
'OMBALTER TABLE '''||replace(tc.table_name,'$','\$')||''' ADD COLUMN '''||replace(tc.column_name,'$','\$')||''' SET PROPERTIES(DATATYPE,NOT_NULL'||
case when tc.data_type='NUMBER' then ',PRECISION,SCALE' when tc.data_type='FLOAT' then ',PRECISION'
 when tc.data_type in ('CHAR','VARCHAR','VARCHAR2','RAW','NCHAR','NVARCHAR','NVARCHAR2') then ',LENGTH'
 when tc.data_type like 'TIMESTAMP%' then ',FRACTIONAL_SECONDS_PRECISION'
 end||
',DESCRIPTION) VALUES('''||case when tc.data_type like 'TIMESTAMP%' then 'TIMESTAMP' else tc.data_type end||
''','''||case tc.nullable when 'Y' then 'false' else 'true' end||''''||
case when tc.data_type='NUMBER' then ','''||tc.data_precision||''','''||tc.data_scale||''''
 when tc.data_type='FLOAT' then ','''||tc.data_precision||''''
 when tc.data_type in ('CHAR','VARCHAR','VARCHAR2','RAW','NCHAR','NVARCHAR','NVARCHAR2') then ','''||tc.data_length||''''
 when tc.data_type like 'TIMESTAMP%' then ','''||trim(')' from trim('(' from regexp_substr(tc.data_type,'\(.*\)')))||''''
 end||
','''||replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(ccm.comments,'\','\\'),';','\;'),'''',''''''),'"','\"'),'[','\['),']','\]'),'{','\{'),'}','\}'),'$','\$'),chr(10),'\n'),chr(9),'\t')||''')'
omb_command
 from user_tab_columns tc,user_tab_comments tcm,user_col_comments ccm
where tc.data_type not like 'LONG%' and tc.table_name=tcm.table_name
and tc.table_name=ccm.table_name and tc.column_name=ccm.column_name
--and tc.table_name like 'A%'
order by tc.table_name,tc.column_id;

set pages 0
clear breaks
clear columns
ttitle off

select 'puts "created all tables, [expr {[clock seconds]-$StartTime}] seconds"' omb_command from dual;
select 'OMBCOMMIT' omb_command from dual;

select 'OMBALTER TABLE '''||replace(table_name,'$','\$')||''' ADD '||case when constraint_type='P' then 'PRIMARY_KEY' else 'UNIQUE_KEY' end||
' '''||replace(constraint_name,'$','\$')||''' SET REFERENCE COLUMNS ('||
replace("OWBLand_Get_Constraint_Columns"(table_name,constraint_name),'$','\$')||')' omb_command
 from user_constraints
where constraint_type in ('P','U')
--and table_name like 'A%'
order by table_name,constraint_type,constraint_name;

select 'puts "created all primary/unique keys, [expr {[clock seconds]-$StartTime}] seconds"' omb_command from dual;
select 'OMBCOMMIT' omb_command from dual;

select 'OMBALTER TABLE '''||replace(c.table_name,'$','\$')||''' ADD FOREIGN_KEY '''||replace(c.constraint_name,'$','\$')||''' SET REFERENCE COLUMNS ('||
replace("OWBLand_Get_Constraint_Columns"(c.table_name,c.constraint_name),'$','\$')||') SET REFERENCE '||
case when refc.constraint_type='P' then 'PRIMARY_KEY' else 'UNIQUE_KEY' end||' '''||
replace(c.r_constraint_name,'$','\$')||''' OF TABLE '''||replace(refc.table_name,'$','\$')||'''' omb_command
 from user_constraints c,user_constraints refc
where c.constraint_type='R' and c.r_owner=refc.owner and c.r_constraint_name=refc.constraint_name
--and c.table_name like 'AA%'
order by c.table_name,c.constraint_type,c.constraint_name;

select 'puts "created all foreign keys, [expr {[clock seconds]-$StartTime}] seconds"' omb_command from dual;
select 'OMBCOMMIT' omb_command from dual;


spool off




set heading on
set feedback 5
set pages 24
set echo on


Then run script gen_model.sql under SQL*Plus:

@gen_model.sql
As a result you got OMB script (in my case it was owb_temp.tcl file in the root of dick C:) which you can run under OMB*Plus with source command. Before executing this script change current context to corresponding source module (with OMBCC command).