In article <722kvp$vl0$1_at_nnrp1.dejanews.com>,
mjain_at_my-dejanews.com wrote:
> *** A copy of this message is also sent to <jcman_at_worldnet.att.net>
>
> Try free shareware called TOAD!! It is excellent and can let you
> do what trying to do!!
>
> With TOAD it is a breeze!!
>
> Go to website http://www.toadsoft.com/ and download the tool
> from this site!!
>
> You will be glad that you did it!! I tried it and I am very
> happy with this tool.
>
> Thanks to TOADMAN!!
>
> Hope this helps,
>
> Manoj Jain
> Oracle Certified Professional - OCP DBA
> Chauncey Certified Oracle7 DBA
>
> In article <721vg7$cie_at_bgtnsc02.worldnet.att.net>,
> "Jerry" <jcman_at_worldnet.att.net> wrote:
> > Can anyone share with me a script that would read the data dictionary and
> > generate the DDL necessary to create those tables? Indexes would be nice,
> > too. Thanks in advance.
> >
> >
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
Yes, it is a very fine tool indeed - only it's not free anymore - as you'll
see when you connect to the above mentioned www.toadsoft.com
- In the mean time, have a look at the following, which runs as a Unix
shellsript and uses the gentable.sql file for SQL*plus
schema=$1
table=$2
sqlplus ingesdo/ingesdo >${table}.tmp <<EOF
set tab off
set serveroutput on
set heading off
set pagesize 0
set feedback off
exec gentable('${schema}', '${table}')
exit
EOF
# cut -c 4- ${table}.tmp
sed '1,$s/^-- //g' ${table}.tmp
mv ${table}.tmp $GR_HOME/sql/${table}.sql
gentable.sql
- ----
Procedure to generate a script that will
---- recreate a table and all it's dependent
---- objects (constraints, indexes, grants).
---
create or replace procedure gentable (own in varchar2, tab in varchar2) as
t user_tables%type;
i user_indexes%type;
varline varchar2(200);
maxcollength number;
curcol number;
colcount number;
curconscol number;
conscolscount number;
conscolvar varchar2(200);
pk_table varchar2(200);
un varchar2(10);
goption varchar2(50);
cursor cols(own in varchar2, tab in varchar2) is
select * from user_tab_columns
where owner = upper(own) and table_name = upper(tab);
cursor cons(own in varchar2, tab in varchar2) is
select * from user_constraints
where owner = upper(own)
and table_name = upper(tab)
order by constraint_type;
cursor conscols(own in varchar2, cons in varchar2) is
select * from user_cons_columns
where owner = own
and constraint_name = cons
order by position;
cursor ind(own in varchar2, tab in varchar2) is
select * from user_indexes
where owner = own
and table_name = tab
and index_name not in (select constraint_name from
user_constraints
where owner = own
and constraint_name = index_name);
cursor icols(own in varchar2, ind in varchar2) is
select * from user_ind_columns
where index_owner = own
and index_name = ind
order by column_position;
cursor privs(own in varchar2, tab in varchar2) is
select * from user_tab_privs
where owner = own
and table_name = tab;
cursor tabcom(own in varchar2, tab in varchar2) is
select * from user_tab_comments
where owner = own
and table_name = tab
and comments is not null;
cursor colcom(own in varchar2, tab in varchar2) is
select * from user_col_comments
where owner = own
and table_name = tab
and comments is not null;
procedure out(string in varchar2) is
begin
dbms_output.put_line('-- ' || string);
end;
begin dbms_output.enable (100000);
- Create table
--
- get table details
select * into t
from user_tables
where owner= upper(own)
and table_name=upper(tab);
out('create table ' || t.table_name || ' (');
- get maximum length of the column name (for nice formatting)
select max(length(column_name)), count(*)
into maxcollength, colcount
from user_tab_columns
where owner=upper(own)
and table_name=upper(tab);
curcol := 0;
for c in cols(own, tab)
loop curcol := curcol + 1;
varline := rpad('"' || c.column_name || '"', maxcollength+4);
varline := varline || c.data_type;
- data length and precision
if c.data_type in ('VARCHAR', 'VARCHAR2', 'CHAR') then
varline := varline || '(' || c.data_length || ')';
elsif c.data_type in ('NUMBER', 'FLOAT') then
varline := varline || '(' || c.data_precision;
if c.data_scale <> 0 then
varline := varline || ', ' || c.data_scale;
end if;
varline := varline || ')';
end if;
- NOT NULL ?
if c.nullable <> 'Y' then
varline := varline || ' ' || 'not null';
end if;
- don't print the last comma
if curcol <> colcount then
varline := varline || ',';
end if;
out(' ' || varline);
end loop; out(')');
- other parameters
out(' pctfree ' || t.pct_free);
out(' pctused ' || t.pct_used);
out(' initrans ' || t.ini_trans);
out(' maxtrans ' || t.max_trans);
out(' tablespace ' || t.tablespace_name);
out(' storage (');
out(' initial ' || t.initial_extent);
out(' next ' || t.next_extent);
out(' minextents ' || t.min_extents);
out(' maxextents ' || t.max_extents);
out(' pctincrease ' || t.pct_increase);
--out(' freelists ' || t.freelists);
--out(' freelist groups ' || t.freelist_groups);
out(' )'); out(';'); out('');
- Add Constraints
--
for co in cons(t.owner, t.table_name)
loop
------ Check constraints ------------------------
if co.constraint_type = 'C'
and co.search_condition not like '% IS NOT NULL' then
out('alter table ' || t.table_name);
out(' add constraint ' || co.constraint_name);
out(' check (' || co.search_condition || ')');
out(';'); out('');
------ Referential constraints ------------------
elsif co.constraint_type = 'R' then
out('alter table ' || t.table_name);
out(' add constraint ' || co.constraint_name);
out(' foreign key (');
curconscol := 0;
select count(*) into conscolscount
from user_cons_columns
where owner = t.owner
and table_name = t.table_name
and constraint_name = co.constraint_name;
for cc in conscols(t.owner, co.constraint_name)
loop
curconscol := curconscol + 1;
conscolvar := '"' || cc.column_name || '"';
if curconscol <> conscolscount then
conscolvar := conscolvar || ',';
end if;
out(' ' || conscolvar);
end loop;
out(' )');
- get primary key info
select table_name
into pk_table
from user_constraints
where constraint_name = co.r_constraint_name;
out(' references ' || co.r_owner || '.' || pk_table || ' (');
curconscol := 0;
select count(*)
into conscolscount
from user_cons_columns
where owner = t.owner
and constraint_name = co.r_constraint_name;
for pkc in conscols(t.owner, co.r_constraint_name)
loop
curconscol := curconscol + 1;
conscolvar := '"' || pkc.column_name || '"';
if curconscol <> conscolscount then
conscolvar := conscolvar || ',';
end if;
out(' ' || conscolvar);
end loop;
out(' )');
if co.delete_rule = 'CASCADE' then
out(' on delete cascade');
end if;
out(';');
out('');
------ Primary keys -----------------------------
elsif co.constraint_type = 'P' then
out('alter table ' || t.table_name);
out(' add constraint ' || co.constraint_name);
out(' primary key (');
curconscol := 0;
select count(*)
into conscolscount
from user_cons_columns
where owner = t.owner
and table_name = t.table_name
and constraint_name = co.constraint_name;
for cc in conscols(t.owner, co.constraint_name)
loop
curconscol := curconscol + 1;
conscolvar := '"' || cc.column_name || '"';
if curconscol <> conscolscount then
conscolvar := conscolvar || ',';
end if;
out(' ' || conscolvar);
end loop;
out(' )');
select *
into i
from user_indexes
where owner = t.owner
and index_name = co.constraint_name;
out(' using index');
out(' tablespace ' || i.tablespace_name);
out(' storage (');
out(' initial ' || i.initial_extent);
out(' next ' || i.next_extent);
out(' minextents ' || i.min_extents);
out(' maxextents ' || i.max_extents);
out(' pctincrease ' || i.pct_increase);
--out(' freelists ' || i.freelists);
--out(' freelist groups ' || i.freelist_groups);
out(')');
out(';');
out('');
------ Unique constraint -------------------------
elsif co.constraint_type = 'U' then
out('alter table ' || t.table_name);
out(' add constraint ' || co.constraint_name);
out(' unique (');
curconscol := 0;
select count(*)
into conscolscount from user_cons_columns
where owner = t.owner
and table_name = t.table_name
and constraint_name = co.constraint_name;
for cc in conscols(t.owner, co.constraint_name)
loop
curconscol := curconscol + 1;
conscolvar := '"' || cc.column_name || '"';
if curconscol <> conscolscount then
conscolvar := conscolvar || ',';
end if;
out(' ' || conscolvar);
end loop;
out(' )');
select *
into i
from user_indexes
where owner = t.owner
and index_name = co.constraint_name;
out(' using index');
out(' tablespace ' || i.tablespace_name);
out(' storage (');
out(' initial ' || i.initial_extent);
out(' next ' || i.next_extent);
out(' minextents ' || i.min_extents);
out(' maxextents ' || i.max_extents);
out(' pctincrease ' || i.pct_increase);
--out(' freelists ' || i.freelists);
--out(' freelist groups ' || i.freelist_groups);
out(')');
------ What might this be ?
elsif co.constraint_type = 'V' then
null;
end if;
end loop;
- Add Indexes
--
for i in ind(t.owner, t.table_name)
loop
if i.uniqueness = 'UNIQUE' then
un := 'unique';
else un := '';
end if;
out('create ' || un || ' index ' || i.index_name);
out(' on ' || i.table_name || ' (');
curconscol := 0;
select count(*)
into conscolscount
from user_ind_columns
where index_owner = i.owner
and index_name = i.index_name;
for ic in icols(i.owner, i.index_name)
loop
curconscol := curconscol + 1;
conscolvar := '"' || ic.column_name || '"';
if curconscol <> conscolscount then
conscolvar := conscolvar || ',';
end if;
out(' ' || conscolvar);
end loop;
out(' )');
out(' tablespace ' || i.tablespace_name);
out(' storage (');
out(' initial ' || i.initial_extent);
out(' next ' || i.next_extent);
out(' minextents ' || i.min_extents);
out(' maxextents ' || i.max_extents);
out(' pctincrease ' || i.pct_increase);
--out(' freelists ' || i.freelists);
--out(' freelist groups ' || i.freelist_groups);
out(')');
out(';');
out('');
end loop;
- Add Grants
--
for p in privs(t.owner, t.table_name)
loop
if p.grantable = 'YES' then
goption := ' with grant option';
end if;
out('grant ' || p.privilege || ' on ' || p.table_name || ' to ' ||
p.grantee || goption || ';');
end loop;
out('');
- Add Comments
--
for tc in tabcom(t.owner, t.table_name)
loop
out('comment on table ' || t.table_name || ' is ''' || tc.comments ||
'''');
end loop;
out(''); for ccom in colcom(t.owner, t.table_name)
loop
out('comment on column ' || t.table_name || '.' || ccom.column_name ||
' is ''' || ccom.comments || '''');
end loop; out('');
end;/
show errors
Regards
--
Oliver Willandsen
European Commission
http://europa.eu.int
All comments represent my own opinion and may not in any circumstance
be regarded as stating an official position of the European Commission
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Nov 09 1998 - 07:50:22 CST
