Verifica Objetos Invalidos

Verificar Objetos Invalidos

para recompilar objetos invalidos Linux
@?/rdbms/admin/utlrp 

para recompilar objetos invalidos Windows
@?\rdbms\admin\utlrp 


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


select 'alter ' || lower(decode(object_type,'PACKAGE BODY','package',object_type)) || ' ' || owner || '.' || object_name || ' ' || decode (object_type,'PACKAGE BODY','compile body','compile') || ';' as "Validacao dos Objetos" from dba_objects where status <> 'VALID';



select
'alter ' || lower(decode(object_type,
'PACKAGE BODY','package',object_type)) || ' ' || owner || '.' || object_name || ' ' || decode (object_type,'PACKAGE BODY','compile body','compile') || ';' AS v_sql
FROM dba_objects
where status <> 'VALID';


DECLARE
BEGIN
FOR valid IN (select 'alter ' || lower(decode(object_type, 'PACKAGE BODY', 'package', object_type)) || ' ' || owner || '.' || object_name || ' ' || decode (object_type, 'PACKAGE BODY', 'compile body', 'compile') || ';' AS valida FROM dba_objects where status <> 'VALID')
    LOOP
    EXECUTE IMMEDIATE valid.valida;
  END LOOP;
END;
/



---------------------------------------------------------------------------------------------------------------------------------------------------------
-- Limpar todos os usuarios que estiverem na lista
---------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
  'DROP ' || object_type || ' ' || owner ||'.'|| object_name || DECODE ( object_type, 'TABLE', ' CASCADE CONSTRAINTS PURGE;',';' ) AS v_sql
FROM
  dba_objects
WHERE
  object_type IN ( 'TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'SYNONYM', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SEQUENCE')
and
  owner in ('SIDER')
ORDER BY
  object_type, object_name;

---------------------------------------------------------------------------------------------------------------------------------------------------------
-- Limpar todos os usuarios que estiverem na lista
---------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
BEGIN
FOR r1 IN ( SELECT
                'DROP ' || object_type || ' ' || owner ||'.'|| object_name || DECODE ( object_type, 'TABLE', ' CASCADE CONSTRAINTS PURGE',' ') AS v_sql
            FROM
                dba_objects
            WHERE
                object_type IN ( 'TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'SYNONYM', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SEQUENCE')
            and
                owner IN ('SIDER')
            ORDER BY
                object_type, object_name )
            LOOP
    EXECUTE IMMEDIATE r1.v_sql;
  END LOOP;
END;
/

set lines 200 pages 200;
col object_name for a30;
col owner for a15;
alter session set nls_date_format='dd/mm/yy hh24:mi';
set timing on;
set feedback off;
select
    owner
    ,object_name
    ,object_type
    ,created
from     dba_objects
where     status = 'INVALID'
and     owner not in ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','MDSYS','LBACSYS','SYSMAN','OLAPSYS','TSMSYS','DMSYS','ORDSYS','SCOTT','WKSYS','SOE','PERFSTAT')
and     object_name not like 'BIN\$%'
order by 1,2;