-- RECOMPILA TODOS OS OBJETOS
# exec sys.utl_recomp.recomp_serial();

SELECT status, count(*) FROM dba_objects GROUP BY status;
SELECT owner, status , count(*) FROM dba_objects WHERE status <> 'VALID' GROUP BY owner, status;
SELECT owner, object_name, object_type status FROM dba_objects WHERE status <> 'VALID' ORDER BY 1,3 ;

ttitle left skip 1 "QUANTIDADE DOS OBJETOS INVÁLIDOS" skip 2
SET lines 125 pages 300
clear columns
COL owner       for a20
COL object_type for a20
SELECT  owner,
		object_type,
		status,
		count(*) "TOTAL"
FROM 	dba_objects 
WHERE status <> 'VALID' 
GROUP BY owner, object_type, status
ORDER BY owner, object_type;
ttitle off;

ttitle left skip 1 "LISTA DOS OBJETOS INVÁLIDOS" skip 2
SET pagesize 300
SET lines 125
COL owner for a30
COL object_type for a20
COL object_name for a40
SELECT	owner,
        object_name,
	    object_type,
	    status
FROM 	dba_objects 
WHERE status='inVALID' 
AND OWNER = 'BIL' 
ORDER BY owner, object_name, object_type;
ttitle off;

SELECT 'ALTER ' 
		|| OBJECT_TYPE 
		|| ' ' 
		|| OWNER || '.' 
		|| OBJECT_NAME 
		|| ' COMPILE;' 
FROM 
	DBA_OBJECTS 
WHERE STATUS <> 'VALID';

SELECT line,text FROM dba_source WHERE NAME ='P_ABTCCAPER';