Re: looking for schema re-engineering script

From: Eric Givler <egivler_at_flash.net>
Date: 2000/07/19
Message-ID: <ssgd5.11056$zW2.203562@news.flash.net>

I found this lying around. It doesn't look like it does everything, but it's a start.

Rem PANIC.SQL

set pages 0
set feedback off

Rem
Rem Create All Tablespaces.
Rem

select 'create tablespace ' || T.tablespace name || chr(10) ||

       'datafile ''' || F.file name || ''' size ' || to char(F.bytes/1048576)

       || 'M' || chr(10) ||
       'default  storage (Initial ' || to char(T.initial extent) ||
       ' next ' || to char(T.next extent) || ' minextents ' || 
       to char(T.min extents) || chr(10) ||
       '         maxextents ' || to char(T.max extents) || ' pctincrease 
' || 
       to char(T.pct increase) || ') online;' 
from 
       sys.dba data files F,
       sys.dba tablespaces  T
where 
       T.tablespace name  = F.tablespace name
and    T.tablespace name != 'SYSTEM'
and    F.file id          = ( select min(file id)
                              from   sys.dba data files
                              where  tablespace name = 

T.tablespace name )
/

Rem
Rem Create All Tablespace Datafile Extents. Rem

select 'alter tablespace ' || T.tablespace name || chr(10) ||

       'add datafile ''' || F.file name || ''' size ' || 
       to char(F.bytes/1048576) || 'M;'
from
       sys.dba data files F,
       sys.dba tablespaces  T
where
       T.tablespace name = F.tablespace name
and    F.file id        != ( select min(file id)
                             from   sys.dba data files
                             where  tablespace name = 

T.tablespace name )
/

Rem
Rem Create System Roles
Rem

select 'create role '|| role ||

       decode(password required,'N',' not identified;',
                                    ' identified externally;')

from sys.dba roles
/

Rem
Rem Create System Profiles
Rem

select distinct 'create profile ' || profile || ' limit ' || ';' from sys.dba profiles
/

select 'alter role ' || profile || ' limit ' ||

        resource name || ' ' || limit || ';' from sys.dba profiles

where   limit   != 'DEFAULT'
and  (  profile != 'DEFAULT' 
     or limit   != 'UNLIMITED' )

/

Rem
Rem Create ALL User Connections
Rem

select 'create USER ' || username ||
       ' identified by XXXXX ' || chr(10) ||
       ' default tablespace ' || default tablespace ||
       ' temporary tablespace '|| temporary tablespace || chr(10) ||
       ' quota unlimited on ' || default tablespace     || ' ' ||
       ' quota unlimited on ' || temporary tablespace   || ';'

from sys.dba users
where username not in ('SYSTEM','SYS',' NEXT USER','PUBLIC')
/

Rem
Rem Reset User Passwords
Rem

select 'alter USER ' || username || ' identified by values ''' ||

       password || ''';'
from sys.dba users
where username not in ('SYSTEM','SYS',' NEXT USER','PUBLIC') and password != 'EXTERNAL'
/

Rem
Rem Create Tablespace Quotas
Rem

select 'alter USER ' || username || ' quota ' ||

       decode(max bytes,-1,'unlimited',to char(max bytes/1024) ||' K') ||

       ' on tablespace '|| tablespace name ||';' from sys.dba ts quotas
/

Rem
Rem Grant System Privileges
Rem

select 'grant ' || S.name || ' to ' || U.username || ';' from system privilege map S,

       sys.sysauth$ P,
       sys.dba users U
where  U.user id    = P.grantee#

and P.privilege# = S.privilege
and P.privilege# < 0
/

Rem
Rem Grant System Roles
Rem

Select 'grant ' || X.name || ' to ' || U.username || ';' From sys.user$ X,

       sys.dba users U
where X.user# IN ( select privilege#

                     From    sys.sysauth$ 
                     connect 
                          by grantee#   = prior privilege# 
                         and privilege# > 0
                      start 
                        with grantee#  in (1, U.user id )
                         and privilege# > 0 
                   )  

/

Rem
Rem Create All PUBLIC Synonyms
Rem

select 'create public synonym ' || synonym name || ' for ' ||
       decode(table owner,'','',table owner||'.') || table name || 
       decode(db link,'','','@'||db link) || ';'

from sys.dba synonyms
where owner = 'PUBLIC'
and table owner != 'SYS'
/

Rem
Rem Create ALL Public Database Links
Rem

select 'create public database link ' || db link || chr(10) ||

       'connect to ' || username || ' identified by XXXXXX using ''' ||
       host || ''';'

from sys.dba db links
where owner = 'PUBLIC'
/

Rem
Rem Create Rollback Segments
Rem

select 'create rollback segment ' || segment name || 
       ' tablespace ' || tablespace name || chr(10) ||
       'storage (initial ' || to char(initial extent) ||
       ' next ' || to char(next extent) || ' minextents ' ||
       to char(min extents) || chr(10) ||
       ' maxextents ' || to char(max extents) || ') ' ||
       status || ';'

from sys.dba rollback segs
where segment name != 'SYSTEM'
/

  Patrick Dewael <dewael.p_at_pg.com> wrote in message news:396DDD63.269816CD_at_pg.com...
  Hi,
  I'm currently looking for a script that catches/re-engineers generically all (or nearly all) objects from a certain schema for an Oracle 8i environment. It has to include the LOB storage settings. Can anyone help me ?

  The objects necessary to be covered:

    a.. CREATE USER statement 
    b.. tables + storage parameters 
    c.. indexes + storage parameters 
    d.. views 
    e.. triggers 
    f.. constraints 
    g.. grants 
    h.. packages/procedures and function 
    i.. synonyms 

  Thanks,
  Patrick     

--

Received on Wed Jul 19 2000 - 00:00:00 CDT