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