Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Scripts -- Scripts -- Scripts

RE: Scripts -- Scripts -- Scripts

From: <Brian_McQuillan_at_gelco.com>
Date: Fri, 09 Feb 2001 11:27:47 -0800
Message-ID: <F001.002B0A8F.20010209110222@fatcity.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US