Chequeo General de la "Salud" de la base de Datos (Oracle Database Health)

El script que copié abajo permite tener una primera idea del estado de la base de datos. Se puede ejecutar para realizar un reporte de los objetos que componen la base. Revisa alocaciones, invalidaciones, definición de segmentos no default, db_link
no accesibles, etc.

Rem DB Health (by Pablo A. Rovedo)
Rem ---------------------------------------------------
Rem Created    :   2007.09.13
Rem Function   :   Script para chequear el estado general de una base
Rem                de datos (Infraestructura)
Rem
Rem
Rem Modificated:   2008.07.16  - Se agreron puntos 28,29, 30 y 31
Rem Modificated:   2008.06.24  - Se agregaron puntos 15,23..27           Rovedo P.                             
Rem Modificated:   2008.06.23  - Se agregaron puntos 13,14 y 16          Rovedo P.          
Rem Modificated:   2007.09.24  - Se agregaron puntos 17..23              Rovedo P.
Rem Modificated:   2008.07.30  - Se agregaron puntos 24..31              Rovedo P.
Rem



Rem 1 - Usuarios con tablespace SYSTEM como temporary tablespace (exceptuando users SYS y SYSTEM)
Rem ---------------------------------------------------------------------------------------------

select username,default_tablespace
from dba_users
where default_tablespace = 'SYSTEM'
  and username not in ('SYS','SYSTEM')
/


Rem 2 - Usuarios con tablespace SYSTEM como default tablespace (exceptuando users SYS y SYSTEM)
Rem -----------------------------------------------------------------------------------------

select username,temporary_tablespace
from dba_users
where temporary_tablespace = 'SYSTEM'
  and username not in ('SYS','SYSTEM')
/


Rem 3 - Indices UNUSABLES
Rem ---------------------

select owner,count(1)
from dba_indexes
where status = 'INVALID'
group by owner
/

select owner,
       index_name,
       index_type,
       table_owner,
       table_name,
       table_type,
       tablespace_name
from dba_indexes
where status = 'INVALID'
/


Rem 4 - Objetos invalidos
Rem ---------------------

select owner,object_type,count(1)
from dba_objects
where status = 'INVALID'
group by owner,object_type
order by 1,2
/

select owner,
       object_name,
       created,
       last_ddl_time
from dba_objects
where status = 'INVALID'
order by 1,2
/


Rem 5 - Paquetes con bodies sin que no tengan sus correspondientes headers
Rem ----------------------------------------------------------------------

select unique owner,name
from dba_source a
where type = 'PACKAGE BODY'
  and not exists (select null
                  from dba_source b
                  where a.owner = b.owner
                    and a.name = b.name
                    and b.type = 'PACKAGE')
/


Rem 6 - Constraints deshabilitadas
Rem ------------------------------

select owner,
       case constraint_type
          when 'P' then 'PRIMARY_KEY'
          when 'R' then 'FOREIGN_KEY'
          when 'U' then 'UNIQUE'
          when 'C' then 'CHECK'
       end constraint_type,  
       count(1)
from dba_constraints
where status = 'DISABLED'
group by owner,constraint_type
order by 1,2
/


select owner,
       constraint_name,
       constraint_type,
       table_name
from dba_constraints
where status = 'DISABLED'
order by 1,2
/


Rem 7 - Triggers deshabilitados
Rem ---------------------------

select owner,
       trigger_name,
       trigger_type,
       triggering_event,
       table_owner,
       table_name
from dba_triggers
where status = 'DISABLED'
order by 1,2
/


Rem 8 - Controlar que sys.aud$ no este en tablespace SYSTEM
Rem -------------------------------------------------------

select name,
       value,
       display_value,
       description
from v$parameter
where name like 'audit%'
/

select owner,
       segment_name,
       tablespace_name
from dba_segments
where segment_name = 'AUD$'
/


Rem 9 - Jobs en estado broken
Rem -------------------------

select * from dba_jobs
where broken = 'Y'
/


Rem 10 - Jobs con next_date menor a sysdate
Rem ---------------------------------------

select *
from dba_jobs
where sysdate > next_date
/


Rem 11 - Jobs con fallas
------------------------

select *
from dba_jobs
where failures > 0
/


Rem 12 - Roles no otorgados a ningun rol o user
Rem -------------------------------------------

select role from dba_roles
minus
select granted_role from dba_role_privs
/

Rem 13 - Sinonimos publicos que apuntan a objetos inexistentes
Rem ----------------------------------------------------------

select * from dba_synonyms a
where owner = 'PUBLIC'
  and not exists (select null
                  from dba_objects b
                  where a.table_owner = b.owner
                    and a.table_name  = b.object_name)
/


Rem 14 - Sinonimos privados que apuntan a objetos inexistentes
Rem ----------------------------------------------------------

select * from dba_synonyms a
where owner != 'PUBLIC'
  and not exists (select null
                  from dba_objects b
                  where a.table_owner = b.owner
                    and a.table_name  = b.object_name)
/

Rem 15 - Database links que son inaccesibles
Rem ----------------------------------------

begin
    for i in (select decode(owner,'PUBLIC',user,owner) owner,db_link from dba_db_links)
    loop
       begin
           execute immediate 'create view 'i.owner'.TEST as select count(1) c from dual@'i.db_link;
           dbms_output.put_line('OWNER: 'i.owner'; DB_LINK: 'i.db_link' --> ACCESIBLE');
           execute immediate 'drop view 'i.owner'.TEST';
           exception
             when others then
                 dbms_output.put_line('OWNER: 'i.owner'; DB_LINK: 'i.db_link' --> NO ACCESIBLE');
       end;
    end loop;
end;
/

Rem 16 - Segmentos con mas de 100 extents (excluir sys y system)
Rem ----------------------------------------------------------------

select * from dba_segments
where extents > 100
  and owner not in ('SYS','SYSTEM')
/


Rem 17 - Tablas no analizadas con mas de 1000 registros
-------------------------------------------------------

set serverout on size 500000
declare
    l_cnt int;
begin
    for i in (select * from dba_tables
               where last_analyzed is null
                 and owner not in ('SYS','SYSTEM'))
    loop
          execute immediate 'select count(1) from 'i.owner'.'i.table_name
          into l_cnt;
          if (l_cnt > 1000) then
             dbms_output.put_line(i.owner'.'i.table_name);
          end if;
    end loop;
end;
/


Rem 18 - Tablas con mas del 1% de chained rows
Rem ------------------------------------------

select owner,table_name,num_rows,chain_cnt from dba_tables
where owner not in ('SYS','SYSTEM')
  and chain_cnt/num_rows > 0.01
  and num_rows > 0
/


Rem 19 - Tablas con mas de 5 indices
Rem --------------------------------

select owner,table_name,count(1)
from dba_indexes
where owner not in ('SYS','SYSTEM')
group by owner,table_name
having count(1) > 5
/


Rem 20 - Tablas con indices superfluos
Rem ----------------------------------

select a.index_name  '('  a.cols  ')' cols,
       b.index_name  '('  b.cols  ')' cols
from (select index_name,  table_name,
      rtrim(
            max(decode(column_position,1,column_name,null)) ','
                    max(decode(column_position,2,column_name,null)) ','
                    max(decode(column_position,3,column_name,null)) ','
                    max(decode(column_position,4,column_name,null)) ','
                    max(decode(column_position,5,column_name,null)) ','
                    max(decode(column_position,6,column_name,null)) ','
                    max(decode(column_position,7,column_name,null)) ','
                    max(decode(column_position,8,column_name,null)) ','
                    max(decode(column_position,9,column_name,null)) ','
                    max(decode(column_position,10,column_name,null)) , ',' ) cols
           from user_ind_columns
          group by table_name, index_name ) a,
          (select index_name,  table_name,
                  rtrim(
                        max(decode(column_position,1,column_name,null)) ','
                    max(decode(column_position,2,column_name,null)) ','
                    max(decode(column_position,3,column_name,null)) ','
                    max(decode(column_position,4,column_name,null)) ','
                    max(decode(column_position,5,column_name,null)) ','
                    max(decode(column_position,6,column_name,null)) ','
                    max(decode(column_position,7,column_name,null)) ','
                    max(decode(column_position,8,column_name,null)) ','
                    max(decode(column_position,9,column_name,null)) ','
                    max(decode(column_position,10,column_name,null)) , ',' ) cols
           from user_ind_columns
          group by table_name, index_name ) b
    where a.table_name = b.table_name
      and a.index_name <> b.index_name
      and a.cols like b.cols  '%'
/


Rem 21 - Tablas que no poseean primary key y que no esten vacias
Rem ------------------------------------------------------------

select owner,table_name
from dba_tables a
where owner not in ('SYS','SYSTEM')
  and num_rows > 0
  and not exists (select null
                  from dba_constraints b
                  where a.owner = b.owner
                    and a.table_name = b.table_name
                    and b.constraint_type = 'P')
order by 1,2
/


Rem 22 - Tablas que no tengan los parametros de storage defaults
Rem ------------------------------------------------------------


select a.owner,a.table_name,a.pct_increase,a.initial_extent,a.next_extent,
       a.max_extents,a.min_extents,b.tablespace_name
from dba_tables a,
     dba_tablespaces b
where a.tablespace_name = b.tablespace_name
  and a.owner not in ('SYS','SYSTEM')
  and (a.pct_increase != b.pct_increase
       or a.initial_extent != b.initial_extent
       or a.next_extent != b.next_extent
       or a.max_extents != b.max_extents
       or a.min_extents != b.min_extents)
/


Rem 23 - Tablas con valores pct_free o pct_used no defaults
Rem -------------------------------------------------------


select owner,table_name
from dba_tables
where  owner not in ('SYS','SYSTEM')
  and (pct_free != 10
       or pct_used != 40)
/


Rem 24 - Indices no analizados
Rem --------------------------

select owner,index_name,table_name
from dba_indexes
where owner not in ('SYS','SYSTEM')
  and last_analyzed is null


Rem 25 - Foreign keys sin indices asociados (Analizar a nivel usuario)
Rem ---------------------------------------


select decode( b.table_name, NULL, '****', 'ok' ) Status,
     a.table_name, a.columns, b.columns
from
( select substr(a.table_name,1,30) table_name,
     substr(a.constraint_name,1,30) constraint_name,
       max(decode(position, 1,     substr(column_name,1,30),NULL))
       max(decode(position, 2,', 'substr(column_name,1,30),NULL))
       max(decode(position, 3,', 'substr(column_name,1,30),NULL))
       max(decode(position, 4,', 'substr(column_name,1,30),NULL))
       max(decode(position, 5,', 'substr(column_name,1,30),NULL))
       max(decode(position, 6,', 'substr(column_name,1,30),NULL))
       max(decode(position, 7,', 'substr(column_name,1,30),NULL))
       max(decode(position, 8,', 'substr(column_name,1,30),NULL))
       max(decode(position, 9,', 'substr(column_name,1,30),NULL))
       max(decode(position,10,', 'substr(column_name,1,30),NULL))
       max(decode(position,11,', 'substr(column_name,1,30),NULL))
       max(decode(position,12,', 'substr(column_name,1,30),NULL))
       max(decode(position,13,', 'substr(column_name,1,30),NULL))
       max(decode(position,14,', 'substr(column_name,1,30),NULL))
       max(decode(position,15,', 'substr(column_name,1,30),NULL))
       max(decode(position,16,', 'substr(column_name,1,30),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
       max(decode(column_position, 1,     substr(column_name,1,30),NULL))
       max(decode(column_position, 2,', 'substr(column_name,1,30),NULL))
       max(decode(column_position, 3,', 'substr(column_name,1,30),NULL))
       max(decode(column_position, 4,', 'substr(column_name,1,30),NULL))
       max(decode(column_position, 5,', 'substr(column_name,1,30),NULL))
       max(decode(column_position, 6,', 'substr(column_name,1,30),NULL))
       max(decode(column_position, 7,', 'substr(column_name,1,30),NULL))
       max(decode(column_position, 8,', 'substr(column_name,1,30),NULL))
       max(decode(column_position, 9,', 'substr(column_name,1,30),NULL))
       max(decode(column_position,10,', 'substr(column_name,1,30),NULL))
       max(decode(column_position,11,', 'substr(column_name,1,30),NULL))
       max(decode(column_position,12,', 'substr(column_name,1,30),NULL))
       max(decode(column_position,13,', 'substr(column_name,1,30),NULL))
       max(decode(column_position,14,', 'substr(column_name,1,30),NULL))
       max(decode(column_position,15,', 'substr(column_name,1,30),NULL))
       max(decode(column_position,16,', 'substr(column_name,1,30),NULL)) columns
    from user_ind_columns
   group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns  '%'


Rem 26 - Tablespaces con Manejo de Extents por Diccionario
Rem ------------------------------------------------------

select tablespace_name
from dba_tablespaces
where extent_management = 'DICTIONARY'
/


Rem 27 - Tablespaces con Manejo de Segmentos Manual
Rem ------------------------------------------------------

select tablespace_name
from dba_tablespaces
where segment_space_management = 'MANUAL'
  and contents = 'PERMANENT'
/

Rem 28 - Tablas con mas de 100 columnas
Rem -------------------------------------------------------------------

select owner,table_name,count(1)
from dba_tab_columns
where owner not in ('SYS','SYSTEM')
group by owner,table_name
having count(1) > 100            
/

Rem 29 - Owners que comparten tablespaces
Rem ------------------------------------------------------------------

select a.owner,b.owner,
       a.tablespace_name,
       a.cantseg,
       b.cantseg
from (select owner,tablespace_name,count(1) cantseg
      from dba_segments
      where owner not in ('SYS','SYSTEM','SYSMAN','OUTLN','DBSNMP','SYSAUX')
      group by owner,tablespace_name) a,
      (select owner,tablespace_name,count(1) cantseg
      from dba_segments
      where owner not in ('SYS','SYSTEM','SYSMAN','OUTLN','DBSNMP','SYSAUX')
      group by owner,tablespace_name) b
where a.tablespace_name = b.tablespace_name
  and a.owner != b.owner
order by a.cantseg+b.cantseg desc
/

Rem 30 -Tablas Candidatas para Particionar
Rem ------------------------------------------------------------------

select unique a.object_owner,a.object_name
from dba_hist_sql_plan a,
     dba_tab_statistics b
where a.object_owner = b.owner
  and a.object_name = b.table_name
  and a.options = 'FULL'
  and b.num_rows > 10000000
/

Rem 31 - Espacio libre y ocupado en Tablespaces
Rem ------------------------------------------------------------------

column "Name" format a40
column tablespace format a10

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type",
       TO_CHAR(NVL(nvl(a.bytes,b.bytes) / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
       TO_CHAR(NVL(nvl(a.bytes,b.bytes) - NVL(f.bytes, 0),0)/1024/1024, '99G999G990D900') "Used (M)",
       TO_CHAR(NVL((nvl(a.bytes,b.bytes) - NVL(f.bytes, 0)) / nvl(a.bytes,b.bytes) * 100, 0), '990D00') "Used %"
FROM   sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes
        from dba_data_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes) bytes
        from dba_temp_files group by tablespace_name) b,
       (select tablespace_name, sum(bytes) bytes
        from dba_free_space group by tablespace_name) f
WHERE  d.tablespace_name = a.tablespace_name(+) AND
       d.tablespace_name = b.tablespace_name(+) AND
       d.tablespace_name = f.tablespace_name(+)
/