##  DBA Command Post - 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'
/