Re: re-create tables from Data Dictionary
Date: 11 Jun 93 07:42:47 GMT
Message-ID: <1993Jun11.074247.24792_at_dde.dk>
benu_at_cis.umassd.edu (David Hassel) writes:
> Has anyone already written a program (C or perl maybe) to create
>an sql procedure to re-create a table from an existing table using
>all constraints and grants? I've started writing one but maybe some one
>has already done this sort of thing.
> Dave Hassel
>benu_at_cis.umassd.edu
Why not use SQL*Plus? - Might be done like this:
In order to form a propor script, the table must be dropped before it is
generated.
The following script has this effect:
rem Generate drop statements
rem MJ - DDE 7. September 1989
rem run: start gendrop <table name>
rem Change the script if the table name is more than 7 characters
rem
set termout off
set heading off
set verify off
set echo off
set pause off
set pagesize 1000
spool &1.drp.sql
select 'drop table "&1";' from dual where rownum < 2;
spool off
set heading on
set termout on
set echo on
The rownum < 2 clause is present to avoid complications, should the dual consist of more rows.
The table create statement is generated from the user_tab_columns view, in order to guarentee that the sequence of columns corresponds exactly to the one in the export file:
rem Generate table create statements
rem MJ - DDE 7. September 1989
rem run: start gentab <table name>
rem Change the script if the table name is more than 7 characters
rem clusters and constrains are not handled yet
rem
set termout off
set heading off
set verify off
set echo off
set pause off
set pagesize 1000
set linesize 200
column a format a50
column b format a40
column c format a15
column d format a10
column e format a40
spool &1.tab.sql
break on a skip 0
select 'create table "'||max(x.table_name)||'" (' a,
'"'||max(x.column_name)||'" '||max(x.data_type) b,
decode(max(x.data_type),
'NUMBER','('||max(x.data_precision)||','||max(x.data_scale)||')',
'CHAR','('||max(x.data_length)||')',NULL) c,
decode(max(x.nullable),'Y','null','not null') d,
decode(x.column_id, max(y.column_id), ') ', ',') e
from user_tab_columns x, user_tab_columns y
where x.table_name = y.table_name and x.column_id <= y.column_id
and x.table_name = '&1'
and exists (select table_name from user_tables
where table_name = '&1' and cluster_name is null)
group by x.table_name, x.column_id
order by x.column_id;
rem get space information
select 'pctfree '||pct_free f,
'pctused '||pct_used g,
'initrans '||ini_trans h,
'maxtrans '||max_trans i,
'tablespace "'||tablespace_name||'"' j,
'storage (initial '||initial_extent||
' next '||next_extent k,
' minextents '||min_extents||
' maxextents '||max_extents||
' pctincrease '||pct_increase||');' l
from user_tables
where table_name = '&1';
spool off
set heading on
set termout on
set echo on
There may be comments on the table and on its columns. This script will generate the creation script of the comments.
rem Generate comment statements
rem MJ - DDE 7. September 1989
rem run: start gencom <table name>
rem Change the script if the table name is more than 7 characters
rem
set termout off
set heading off
set verify off
set echo off
set pause off
set pagesize 1000
spool &1.com.sql
select 'comment on table "'||table_name||'" is '''||comments||''';'
from user_tab_comments
where table_name = '&1' and comments is not null;
select 'comment on column "'||table_name||'"."'||column_name||
'" is '''||comments||''';'
from user_col_comments
where table_name = '&1' and comments is not null;
spool off
set heading on
set termout on
set echo on
Generate the Index Create Statements
rem Generate index create statements
rem MJ - DDE 7. September 1989
rem run: start geninx <table name>
rem Change the script if the table name is more than 7 characters
rem
set termout off
set heading off
set verify off
set echo off
set pause off
set pagesize 1000
set linesize 200
break on a skip 0
column a format a50
column b format a50
column c format a20
spool &1.inx.sql
select 'create '||max(i.uniqueness)||' index "'||max(i.index_name)||
'" on "'||max(i.table_name)||'" (' a,
'"'||max(c.column_name)||'"' b,
decode(c.column_position, max(y.column_position),
' ) initrans '||max(i.ini_trans), ',') c,
decode(c.column_position, max(y.column_position),
' maxtrans '||max(i.max_trans), null) d,
decode(c.column_position, max(y.column_position),
' tablespace '||max(i.tablespace_name), null) e,
decode(c.column_position, max(y.column_position),
' storage (initial '||max(i.initial_extent), null) f,
decode(c.column_position, max(y.column_position),
' next '||max(i.next_extent), null) g,
decode(c.column_position, max(y.column_position),
' minextents '||max(i.min_extents), null) h,
decode(c.column_position, max(y.column_position),
' maxextents '||max(i.max_extents), null) i,
decode(c.column_position, max(y.column_position),
' pctincrease '||max(i.pct_increase)||');', null) j
from user_indexes i, user_ind_columns c, user_ind_columns y
where i.index_name = c.index_name and c.index_name = y.index_name and c.column_position <= y.column_position and i.table_name = '&1'
group by c.index_name, c.column_position order by c.index_name, c.column_position; spool off
set heading on
set termout on
set echo on
Unfortunately the pctfree parametes in the Create index statement are not stored in the Data Dictionary accessible to the user, so this parameter can not be used in this automated procedure. It is however stored in the Data Dictionary table IND$. Because the asc/desc parameters are not implemented in this version of oracle, the information is not stored in the Data Dictionary, so do not expect this information to be pressent in the generated sql script.
This script will create a script that reflects the grants on the table for different users:
rem Generate table grant statements
rem MJ - DDE 7. September 1989
rem run: start genaut <table name>
rem Change the script if the table name is more than 7 characters
rem
set termout off
set heading off
set verify off
set feedback off
set echo off
set pause off
set pagesize 1000
column a format a50
column b format a20
spool &1.aut.sql
select 'grant index on "'||table_name||'" to "'||grantee||'"' a,
decode(index_priv,'G',' with grant option', null)||';' b
from user_tab_grants_made
where table_name = '&1' and not index_priv = 'N';
select 'grant alter on "'||table_name||'" to "'||grantee||'"' a,
decode(alter_priv,'G',' with grant option', null)||';' b
from user_tab_grants_made
where table_name = '&1' and not alter_priv = 'N';
select 'grant references on "'||table_name||'" to "'||grantee||'"' a,
decode(references_priv,'G',' with grant option', null)||';' b
from user_tab_grants_made
where table_name = '&1' and not references_priv = 'N';
select 'grant update on "'||table_name||'" to "'||grantee||'"' a,
decode(update_priv,'G',' with grant option', null)||';' b
from user_tab_grants_made
where table_name = '&1' and not update_priv = 'N';
select 'grant delete on "'||table_name||'" to "'||grantee||'"' a,
decode(delete_priv,'G',' with grant option', null)||';' b
from user_tab_grants_made
where table_name = '&1' and not delete_priv = 'N';
select 'grant insert on "'||table_name||'" to "'||grantee||'"' a,
decode(insert_priv,'G',' with grant option', null)||';' b
from user_tab_grants_made
where table_name = '&1' and not insert_priv = 'N';
select 'grant select on "'||table_name||'" to "'||grantee||'"' a,
decode(select_priv,'G',' with grant option', null)||';' b
from user_tab_grants_made
where table_name = '&1' and not select_priv = 'N';
spool off
set heading on
set termout on
set echo on
Generate the View Create Statements
rem Generate view create statements
rem MJ - DDE 7. September 1989
rem run: start genview <table name>
rem Change the script if the table name is more than 7 characters
rem
set termout off
set heading off
set verify off
set echo off
set pause off
set pagesize 1000
spool &1.vie.sql
select 'create view "'||r.table_name||'" as ' a, text, ';'
from user_cross_refs r, user_views v
where r.table_name = v.view_name and r.table_type = 'VIEW' and r.ref_table_name = '&1';
spool off
set heading on
set termout on
set echo on
The different spool files are concatenated into one file, and this file are processed by a streem editor, to remove empty lines and spaces:
# Shell script to remove empty lines
\fBsed\fR 's/[ ]*$//
s/[ ][ ]*/ /g
/^$/d'
.S 10 D
The result may look like this:
drop table "TESTTABLE";
create table "TESTTABLE" ("A" CHAR (100) not null,
"B" NUMBER (10,0) null, "C" DATE null, "D" LONG null)
pctfree 10 pctused 40 initrans 1 maxtrans 255 tablespace "SYSTEM" storage (initial 12288 next 12288 minextents 1 maxextents 99 pctincrease 50);
comment on table "TESTTABLE" is 'This is a comment on table testtable'; comment on column "TESTTABLE"."A" is 'This is a comment on column testtable.a';
create UNIQUE index "INX_A" on "TESTTABLE" ( "A" ) initrans 2 maxtrans 255 tablespace SYSTEM storage (initial 12288 next 12288 minextents 1 maxextents 99 pctincrease 50);
create NONUNIQUE index "INX_B" on "TESTTABLE" ( "B" ,
"A" ) initrans 2 maxtrans 255
tablespace SYSTEM storage (initial 12288 next 12288
minextents 1 maxextents 99 pctincrease 50);
grant index on "TESTTABLE" to "SCOTT" with grant option; grant alter on "TESTTABLE" to "SCOTT" with grant option; grant insert on "TESTTABLE" to "SCOTT" ;
create view "TESTVIEW" as
select * from testtable with check option;
--
Martin Jensen, Dansk Data Elektronik A/S, Herlev, Denmark
E-mail: ..!uunet!mcvax!dkuug!dde!mj or mj_at_dde.DK
---
Don't take life too seriously -- you'll never get out if it alive.
Received on Fri Jun 11 1993 - 09:42:47 CEST