Oracledbacr, por simple pasión ...-Copyleft Miembro Comunidad Tecnológica de Oracle Latinoamérica

panicov9.sql
Con este script sql, puedes crear toda la estructura necesaria para recrear una base de datos en algún otro servidor ó simplemente, extraer datos de ubicación de datafiles, seguridad y usuarios.

REM ------------------------------------------------------------------------
REM Autor: Mark Gurry
REM ------
REM
REM Modificado por: Ronald Vargas Quesada, Consultor DBA
REM ---------------
REM
REM Nombre del script: panico.sql
REM ------------------
REM
REM Función: Genera un 'script' con todas las instrucciones necesarias
REM -------- para recrear una base de datos.
REM ----------------------------------------------------------------------
REM Crea el ambiente apropiado para correr
REM ----------------------------------------------------------------------
REM ----------------------------------------------------------------------
REM Crea cascaron de la base de datos a recrear
REM ----------------------------------------------------------------------
set feedback off;
set linesize 0
set pages 0
spool /oracle/files/estructura_bd.sql
prompt set linesize 200
Rem ----------------------------------------------------------------------
Rem Create todos los Tablespaces
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de tablespaces ----------*' from dual;
prompt
select 'create tablespace ' T.tablespace_name ' logging' chr(10)
'datafile ''' F.file_name ''' size ' to_char(F.bytes/1048576)
'M' chr(10)
'AUTOEXTEND ON MAXSIZE UNLIMITED'chr(10)
'EXTENT MANAGEMENT LOCAL SEGMENT'chr(10) 'space MANAGEMENT AUTO;'
from
sys.dba_data_files F,
sys.dba_tablespaces T
where
T.tablespace_name = F.tablespace_name
and T.contents !='UNDO' and
T.contents !='TEMPORARY'
and F.file_id = ( select min(file_id)
from sys.dba_data_files
where tablespace_name = T.tablespace_name );
Rem ----------------------------------------------------------------------
Rem Create Tablespaces UNDO
Rem ----------------------------------------------------------------------
select 'REM *---------- Creacion UNDO Tablespaces ----------*' from dual;
select 'create UNDO tablespace ' T.tablespace_name chr(10)
'datafile ''' F.file_name ''' size ' to_char(F.bytes/1048576)
'M' chr(10)
'AUTOEXTEND ON MAXSIZE UNLIMITED;'
from
sys.dba_data_files F,
sys.dba_tablespaces T
where
T.tablespace_name = F.tablespace_name
and T.contents ='UNDO'
and F.file_id = ( select min(file_id)
from sys.dba_data_files
where tablespace_name = T.tablespace_name );
Rem ----------------------------------------------------------------------
Rem Create todos los Datafiles de los Tablespaces
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de Datafiles ---------* ' from dual;
prompt
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 todos los Roles del sistema
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de Roles ----------*' from dual;
prompt
select 'create role ' role
decode(password_required,'NO',' not identified;',
' identified externally;')
from sys.dba_roles;
Rem ----------------------------------------------------------------------
Rem Create todos los profiles
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de profiles ----------*' from dual;
prompt
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 las conecciones de los usuarios
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de conecciones ----------*' from dual;
prompt
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 Asigna el password a los usuarios
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Asignacion de passwords ----------*' from dual;
prompt
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 quotas en tablespaces
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de quotas ----------*' from dual;
prompt
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 Otorga privilegios del sistema
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Privilegios del sistema ----------*' from dual;
prompt
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
start
with grantee# in (1, U.user_id )
and privilege# > 0 );
Rem ----------------------------------------------------------------------
Rem Creacion sinonimos publicos
Rem ----------------------------------------------------------------------
set linesize 200
prompt
select 'REM *---------- Creacion sinonimos publicos ----------*' from dual;
prompt
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';
set linesize 120
Rem ----------------------------------------------------------------------
Rem Creacion de los database links
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion los database links ----------*' from dual;
prompt
select 'create public database link ' db_link chr(10)
'connect to ' username ' identified by XXXXXX using '''
host ''';'
from sys.dba_db_links;
spool off;
exit