This HTML file was converted from text to HTML by AscToHTM 4.1 (unregistered).
Don't forget to register it
(This message is omitted in the registered version)
rem Generate table creation script
rem
rem Biju Thomas
rem
rem Pass owner name and table name as parameters
rem
set heading off verify off feedback off pages 0 lines 80 trims on
spool /tmp/crtable.sql
set serveroutput on
| declare | |
| / | wuser varchar2 (15) := '&1'; wtable varchar2 (30) := '&2'; /* Tables */ cursor ctabs is select table_name, owner, tablespace_name, initial_extent/1024 initial_extent, pct_free, ini_trans, next_extent/1024 next_extent, pct_increase, pct_used, max_trans, min_extents, max_extents from all_tables where owner like upper(wuser) and table_name like upper(wtable); /* Columns */ cursor ccols (o in varchar2, t in varchar2) is select decode(column_id,1,'(',',') ||rpad(column_name,40) ||rpad(data_type,10) ||rpad( decode(data_type,'DATE' ,' ' ,'LONG' ,' ' ,'LONG RAW',' ' ,'RAW' ,decode(data_length,null,null ,'('||data_length||')') ,'CHAR' ,decode(data_length,null,null ,'('||data_length||')') ,'VARCHAR' ,decode(data_length,null,null ,'('||data_length||')') ,'VARCHAR2',decode(data_length,null,null ,'('||data_length||')') ,'NUMBER' ,decode(data_precision,null,' ' ,'('||data_precision decode(data_scale,null,null,','||data_scale)||')'),'unknown'),8,' ') cstr from all_tab_columns where table_name = upper(t) and owner = upper(o) order by column_id; wcount number := 0; begin dbms_output.enable(100000); for rtabs in ctabs loop wcount := wcount + 1; dbms_output.put_line('create table ' || rtabs.owner || '.' || rtabs.table_name); for rcols in ccols (rtabs.owner, rtabs.table_name) loop dbms_output.put_line(rcols.cstr); end loop; dbms_output.put_line(') pctfree ' || rtabs.pct_free || ' pctused ' || rtabs.pct_used); dbms_output.put_line('initrans ' || rtabs.ini_trans || ' maxtrans ' || rtabs.max_trans); dbms_output.put_line('tablespace ' || rtabs.tablespace_name); dbms_output.put_line('storage (initial ' || rtabs.initial_extent || 'K next ' || rtabs.next_extent || 'K pctincrease ' || rtabs.pct_increase); dbms_output.put_line('minextents ' || rtabs.min_extents || ' maxextents ' || rtabs.max_extents || ' )'); dbms_output.put_line('/'); end loop; if wcount = 0 then dbms_output.put_line('******************************************************'); dbms_output.put_line('* *'); dbms_output.put_line('* Plese Verify Input Parameters... No Matches Found! *'); dbms_output.put_line('* *'); dbms_output.put_line('******************************************************'); end if; end; |
set serveroutput off feedback on verify on pages 999
spool off
prompt
prompt Output saved at /tmp/crtable.sql
This HTML file was converted from text to HTML by AscToHTM 4.1 (unregistered).
Don't forget to register it
(This message is omitted in the registered version)