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(+)
/
.JPG)