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).
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).
