| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Scripts -- Scripts -- Scripts
Pablo,
is this the kind of script you want ? (see below) - this table script doesn't
handle partitions though.
it also uses utl files.
if so mail me back channel and i'll send them to you
hope it helps
Brian.
/* ******************************************************************* */
/* backup_table_defn.sql */
/* ===================== */
/* */
/* B McQuillan 04 FEB 2000 */
/* */
/* Revision | Who | When | What */
/* 0000 | BMcQ | 04-02-2000 | Initial release */
/* ---------|--------|------------|----------------------------------- */
/* ******************************************************************* */
/* **** sql plus settings **** */
set heading off
set pause off
set serveroutput on
set verify off
set echo off
set feedback off
set lines 100
set pages 0
set space 0
set long 4000
/* **** user defined variables **** */
undefine v_schema
undefine v_user
column vEnv new_value vEnv
select value vEnv
from v$parameter
where name = 'background_dump_dest';
column vUTLDir new_value vUTLDir
select value vUTLDir
from v$parameter
where name = 'utl_file_dir';
/* **** main **** */
declare
cursor c_tab is select alta.owner,
alta.table_name,
alta.tablespace_name,
alta.PCT_FREE,
alta.PCT_USED,
alta.INI_TRANS,
alta.MAX_TRANS,
alta.INITIAL_EXTENT,
decode(alta.NEXT_EXTENT,
null, alta.initial_extent,
alta.next_extent) next_extent,
alta.MIN_EXTENTS,
alta.MAX_EXTENTS,
decode(alta.PCT_INCREASE,
null, 0,
alta.pct_increase) pct_increase
from all_tables alta
where alta.owner = upper('&&v_schema')
order by alta.table_name;
vTab c_tab%ROWTYPE;
vLastCol number(3);
f_Outfile utl_file.file_type;
f_FileName varchar2(60);
begin
for vTab in c_tab loop
f_FileName := lower(vtab.owner||'_table_'||vtab.table_name)||'.sql';
if '&vUTLDir' = '*' then
f_outfile := utl_file.FOPEN('&vEnv',f_filename,'A');
else
f_Outfile := utl_file.FOPEN('&vUTLDir',f_filename,'A');
end if;
utl_file.put (f_outfile,'Create table '||vTab.table_name||' (');
utl_file.fflush (f_outfile);
select max(altc.column_id)
into vLastCol
from all_tab_columns altc
where altc.table_name = vtab.table_name
and altc.owner = vtab.owner;
declare
cursor c_tab_col is select substr(astc.column_name,1,30) cname,
substr(decode (astc.data_type,
'NUMBER', 'NUMBER'||'('||
decode(astc.data_scale,
null,
to_char(nvl(astc.data_precision,38))||')',
to_char(nvl(astc.data_precision,38))||','||
to_char(astc.data_scale)||')'),
'VARCHAR','VARCHAR'||'('||to_char(astc.data_length)||')',
'VARCHAR2','VARCHAR2'||'('||to_char(astc.data_length)||')',
astc.data_type),1,15) csize ,
substr(decode (astc.nullable,
'Y', null,
'NOT NULL'),1,8) cnull,
ltrim(rtrim(substr(decode
(astc.column_id,
vLastCol,
')',','),1,10))) ccom
from all_tab_columns astc
where astc.table_name = vtab.table_name
and astc.owner = vtab.owner
order by astc.column_id;
vTabCol c_tab_col%ROWTYPE;
begin
for vTabCol in c_tab_col loop
utl_file.put_line (f_outfile,vTabCol.cname||'
'||vTabCol.csize||' '||vTabCol.cnull||vTabCol.ccom);
end loop;
end;
utl_file.put_line (f_outfile,' pctfree '||vtab.pct_free);
utl_file.put_line (f_outfile,' pctused '||vtab.pct_used);
utl_file.put_line (f_outfile,' initrans '||vtab.ini_trans);
utl_file.put_line (f_outfile,' maxtrans '||vtab.max_trans);
utl_file.put_line (f_outfile,' storage ');
utl_file.put_line (f_outfile,'(initial '||vtab.initial_extent);
utl_file.put_line (f_outfile,' next '||vtab.next_extent);
utl_file.put_line (f_outfile,' minextents '||vtab.min_extents);
utl_file.put_line (f_outfile,' maxextents '||vtab.max_extents);
utl_file.put_line (f_outfile,' pctincrease '||vtab.pct_increase);
utl_file.put_line (f_outfile,')');
utl_file.put_line (f_outfile,'tablespace '||vtab.tablespace_name);
utl_file.put_line (f_outfile,'/');
utl_file.fflush (f_outfile);
utl_file.fclose (f_outfile);
end loop;
end;
/
/* *** END *** */
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Brian_McQuillan_at_gelco.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Feb 09 2001 - 13:27:47 CST
 |
 |