Oracle Diagnósticos Especializados

 

Oracle Infra-Estrutura de Ambientes

Script's de Infra-Estrtutura
Monta script DDL para recriar estruturas primárias do RDBMS

 

/*********GSF********GSF********GSF********GSF********GSF********GSF********/
/***************************************************************************/
/*                                                                         */
/* Propriedade: (Cliente)                                                  */
/* Autor      :                                                            */
/* Data       : 12/03/1998                                                 */
/* GSF*EFB    : Esquema Fisico de Back-End                                 */
/* Arquivo    : RCR_OBJ1.SQL                                               */
/* Descricao  : Monta script DDL para recriar objetos                      */
/* Modulo     : Infra-Estrutura - Estruturas nao Documentadas              */
/* Localizacao: ?path/oracle/7322/ifr                                      */
/* Reponsabilidade    : Celula de Adm. de Banco de Dados                   */
/* Parametros Externos: Nao Ha                                             */
/* Alteracoes Efetuadas:                                                   */
/* SEQ#     DATA      AUTOR / SIGLA                 DETALHAMENTO           */
/* ----   ---------   ----------------------------  -----------------------*/
/*  01    12/Mar/98   Gesualdo Saudino Filho / GSF  Infra-Estrutura        */
/*                                                                         */
/*********GSF********GSF********GSF********GSF********GSF********GSF********/
/***************************************************************************/

/***************************************************************************/
/* NOTA: Sinta-se livre para usar e modificar este roteiro contanto que    */
/*       nao seja vendido ou incluido em qualquer software sem permissao   */  
/***************************************************************************/

pause
prompt 'Tecle (algo) para continuar.... '
set pause on

set pages 0
set feedback off

--
--    Recria todas Tablespaces.
--

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
)
/

--
--    Recria todos Datafiles Extents.
--

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
)
/

--
--    Recria Roles de Sistema
--

select 'create role '|| role ||
       decode(password_required,'N',' not identified;',
                                    ' identified externally;')
from   sys.dba_roles
/

--
--     Recria Profiles de Sistema
--

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' )
/

--
--    Recria todas conexoes de usuarios
--

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')
/

--
--    Reseta Passwords de usuarios
--

select 'alter USER ' || username || ' identified by values ''' ||
       password || ''';'
from   sys.dba_users
where  username not in ('SYSTEM','SYS','_NEXT_USER','PUBLIC')
and    password != 'EXTERNAL'
/
--
--    Recria Quotas de usuarios na Tablespace
--

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
/

--
--    Reconcede Privilegios de Sistema
--

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 / Reconcede Roles de Sistema Select 'grant ' || X.name || ' to ' || U.username || ';' 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
                   )
/

--
--     Recria sinonimos publicos
--

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'
/

--
--    Recria Vinculos de Banco de Dados
--

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'
/

--
--    Recria Segmentos Reconstrutivos
--

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'
/

/*********GSF********GSF********GSF********GSF********GSF********GSF********/
/***************************************************************************/

Aqui você retorna a página Anterior.

1