# 
#  Elaborado por : L.I. Luis Silva Fernandez
#  Modificado por : L.I. Luis Silva Fernandez
#  

set pause off
set lines 1000
set pagesize 10000
set feedback 0

column "% libre"                        format 990.00
column "alias"                          format a20
column "archive#"                       format 999,999,999,999
column "Archivos de Control"            format a40
column "Archivos de redo-log"           format a40
column avg_free                         format 999,990.99
column "Base de Datos"                  format a40
column broken                           format a10
column bytes                            format 999,999,999
column col1                             format a50
column checkpoint                       format 999,999,999
column defa                             format a5
column db_link                          format a25
column "default ts"                     format a20
column error                            format 99999
column ext                              format 999
column "extents libres"                 format 999
column "Fecha del reporte"              format a20
column file_                            format a60
column file_name                        format a40
column ini                              format 999,999
column interval                         format a10
column isdefault                        format a20
column job                              format 999
column key                              format a30
column kb                               format 999,999,990.99
column lastd                            format a8
column libre                            format 99,999.99
column log_user                         format a8
column master_link                      format a20
column max                              format 999,999.00
column maxe                             format 99,999,999,999
column maxfree                          format 999,999.99
column max_nex                          format 9,999,990
column mb                               format 999,990.0
column min_free                         format 990.99
column min_nex                          format 99,990.99
column min                              format 999.00
column name                             format a30
column next                             format 999,999.99
column nextd                            format a8
column next_date                        format a15
column nfree                            format 9,999
column no_dt                            format 999
column object                           format a9
column optimal                          format 99,999,990
column owner                            format a15
column parametro                        format a45
column privilegio                       format a50
column rol                              format a25
column schema                           format a8
column status                           format a10
column sum                              format 999,999,999.00
column sum_free                         format 99,990.99
column "temp ts"                        format a15
column thisd                            format a8
column tot_free                         format 9,990.99
column total                            format 99,999
column ts                               format a20
column type                             format a15
column tipo                             format a20
column tip                              format a3
column upd                              format a3
column username                         format a20
column valor                            format a33
column value                            format 999,999,990.00
column value2                           format 999,999,990
column waits                            format 99
column what                             format a15

compute sum of mb on report
compute sum of total on report
compute sum of no_dt on report
compute sum of libre on report
compute sum of kb on report
break on report

spool tuning.txt

set feedback off
SELECT to_char(sysdate,'dd-mm-yyyy hh24:mi') "Fecha del Reporte"
FROM dual;

prompt
prompt Tamaņo total de la Base de Datos:

SELECT sum(bytes)/1024/1024 Mb, count(*) total, 'datafiles' tipo
FROM dba_data_files
  UNION
SELECT sum((bytes)/1024/1024 * members) mb, count(*) total,
       'grupos log' tipo
FROM v$log
ORDER BY tipo;

SELECT  value/1024 "Tamaņo de Bloque [KB]"
FROM v$parameter
WHERE name='db_block_size';

prompt
prompt Set caracteres, lenguaje y territorio
SELECT name, value$ valor
FROM sys.props$
WHERE name IN ('NLS_CHRACTERSET','NLS_LANGUAGE','NLS_TERRITORY');

prompt
prompt Opciones del kernel
SELECT parameter parametro, value valor
FROM v$option
WHERE value = 'TRUE';

prompt
prompt Estado de la Base
SELECT name, created,
       decode(log_mode,'NOARCHIVELOG','NOARCH','ARCH') logmode,
       CHECKPOINT_CHANGE# CHECKPOINT#, ARCHIVE_CHANGE# ARCHIVE#
FROM v$database;

prompt 
prompt SGA
SELECT name, value/1024 KB
FROM v$sga;

set feedback 1

prompt
prompt Datafiles-Tablespaces
SELECT file_name file_, tablespace_name ts, bytes/1024/1024 Mb
FROM dba_data_files
ORDER BY tablespace_name;

prompt 
prompt Logfiles
SELECT a.group#, member "Archivos de redo-log", a.status,
       bytes/1024/1024 Mb
FROM v$log a, v$logfile b
WHERE a.group#=b.group#
ORDER BY a.group#;

prompt
SELECT name "Archivos de Control"
FROM v$controlfile;

prompt
prompt Segmentos de Rollback [Kb]:
SELECT segment_name Name, tablespace_name ts,
       decode(a.status,'ONLINE','ON',substr(a.status,1,3)) stat,
       extents ext, initial_extent/1024 ini,
       next_extent/1024 next,
       max_extents max, optsize/1024 optimal, waits
FROM dba_rollback_segs a, v$rollstat b
WHERE segment_id=usn(+);

prompt
prompt Objectos que no deben estar en el ts de System
SELECT owner, segment_name Name, segment_type type, bytes/1024 KB
FROM dba_segments
WHERE tablespace_name='SYSTEM'
  AND owner != 'SYS'
ORDER BY owner, segment_type;

prompt Tablespaces sin objectos
SELECT tablespace_name ts
FROM dba_tablespaces
WHERE tablespace_name NOT IN (SELECT DISTINCT tablespace_name
                              FROM dba_segments);

prompt Objectos invalidos
SELECT owner, object_name,object_type, status
FROM dba_objects
WHERE status='INVALID'
ORDER BY owner, object_name, object_type;

prompt Jobs
SELECT job, log_user, schema_user schema,
       to_char(last_date,'dd-mm-yy hh24:mi') lastd,
       to_char(this_date,'dd-mm-yy hh24:mi') thisd,
       to_char(next_date,'dd-mm-yy hh24:mi') nextd,
       broken, interval, what
FROM dba_jobs;

prompt snapshots
SELECT owner, name, master_link, updatable upd, error
FROM dba_snapshots
ORDER BY owner, name;

prompt Snapshots Logs
SELECT log_owner owner, master
FROM dba_snapshot_logs;

prompt DB Links
SELECT owner, db_link, username, host alias
FROM dba_db_links
ORDER BY owner, host, db_link;

prompt Usuarios
SELECT username, default_tablespace "default ts",
       temporary_tablespace "temp ts"
FROM dba_users
ORDER BY username;

prompt Roles
SELECT * from dba_roles;

prompt Roles DBA asignados a usuarios
SELECT grantee, granted_role
FROM dba_role_privs
WHERE granted_role='DBA'
ORDER BY grantee, granted_role;

prompt Parametros
SELECT isdefault defa, name parametro, value valor
FROM v$parameter
ORDER BY name;

prompt ====================================================================
spool off