Oracle Invalid objects .... 

object info 

define OBJECT_NAME='xxxxxxx'

set linesize 300 

col OWNER for a15

col OBJECT_NAME for a25

select  *   from   (select 1 con_id,OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME='&OBJECT_NAME'

                    union 

                    select con_id, OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from cdb_objects where OBJECT_NAME='&&OBJECT_NAME'

                  )

;

======

@$ORACLE_HOME/rdbms/admin/utlrp.sql

utlprp.sql =

DECLARE

   threads pls_integer := &&1;

BEGIN

   utl_recomp.recomp_parallel(threads);

END;

/

declare

threads pls_iteger :=&&1;

begin 

utl_recomp.recomp_parallel(threds, flags =>utl_recomp.oracle_maintained);

end;

or 

define threds=4

exec utl_recomp.recomp_parallel(&threds, flags =>utl_recomp.oracle_maintained);  --- <<<<< Only for Oracle manage objects 

======================

set recsep off

column invalid_object format A30

column likely_reason format A35 word_wrapped

break on type on invalid_object

select owner || '.' || object_name invalid_object,'--- ' || object_type || ' ---' likely_reason from dba_objects

where status = 'INVALID'

union

select d.owner || '.' || d.name,'Non-existent referenced db link ' || d.referenced_link_name from dba_dependencies d

where not exists (select 'x'from dba_db_links

                  where owner in ('PUBLIC', d.owner) 

                  and db_link = d.referenced_link_name

                 )

and d.referenced_link_name is not null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name,'Depends on invalid ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d

where ro.status = 'INVALID'

and ro.owner    = d.referenced_owner

and ro.object_name = d.referenced_name

and ro.object_type = d.referenced_type

and d.referenced_link_name is null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from  dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name,'Depends on newer ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d,dba_objects o

where nvl(ro.last_ddl_time, ro.created) > nvl(o.last_ddl_time, o.created)

and ro.owner = d.referenced_owner

and ro.object_name = d.referenced_name

and ro.object_type = d.referenced_type

and d.referenced_link_name is null

and d.owner  = o.owner

and d.name   = o.object_name

and d.type   = o.object_type

and o.status = 'INVALID'

union

select d.owner || '.' || d.name,'Depends on ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name|| decode(d.referenced_link_name,NULL, '','@' || d.referenced_link_name) from dba_dependencies d

where d.referenced_owner != 'PUBLIC' -- Public synonyms generate noise

and d.referenced_type = 'NON-EXISTENT'

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name invalid_object,'No privilege on referenced ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d 

where not exists (select 'x' from dba_tab_privs p

                  where p.owner = d.referenced_owner

                  and p.table_name = d.referenced_name

                  and p.grantee in ('PUBLIC', d.owner)

                  )

and ro.status = 'VALID'

and ro.owner  = d.referenced_owner

and ro.object_name = d.referenced_name

and d.referenced_link_name is not null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select o.owner || '.' || o.object_name,e.text from dba_errors e,dba_objects o

where e.text like 'PLS-%'

and e.owner  = o.owner

and e.name   = o.object_name

and e.type   = o.object_type

and o.status = 'INVALID';

===============

Components valid / invalid 

set lines 90

col version  for a12

col comp_id  for a8

col comp_name  for a35

col status for  a12

col schema like version

select comp_id,schema,status,version,comp_name from dba_registry 

order by 1;

============

set lines 120

col status   for a9

col object_type  for a20;

col owner.object for a50

select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"

from dba_objects

where 0=0 

and status != 'VALID' 

and object_name not like 'BIN$%' 

order by 4,2;

=====

set serveroutput on

BEGIN

   FOR r

   IN (  SELECT      'alter '

                  || REPLACE (object_type, 'BODY', '')

                  || ' '

                  || owner

                  || '.'

                  || object_name

                  || ' compile '

                  || DECODE (object_type, 'PACKAGE BODY', 'BODY')

                     stmt

           FROM   dba_objects

          WHERE   status = 'INVALID'

       ORDER BY   owner,

               DECODE (object_type,

                          'SYNONYM', 1,

                          'VIEW', 2,

                          'FUNCTION', 3,

                          'PROCEDURE', 4,

                          'PACKAGE', 7,

                          'PACKAGE BODY', 8,

                          9))

   LOOP

      BEGIN

        -- EXECUTE IMMEDIATE (r.stmt);

dbms_output.put_line(r.stmt||' ;');

      EXCEPTION

         WHEN OTHERS

         THEN

            NULL;

      END;

   END LOOP;

END;

/

===

SET PAGESIZE 9999
	clear columns
	clear breaks
	clear computes
	column owner format a25 heading 'Owner'
	column object_name format a30 heading 'Object Name'
	column object_type format a20 heading 'Object Type'
	column count format 999,999 heading 'Count'
	break on owner skip 2 on report
	compute sum label "Count: " of count on owner
	compute sum label "Grand Total: " of count on report
	SELECT
	owner
	, object_type
	, count(*) Count
	FROM dba_objects
	WHERE status <> 'VALID'
	GROUP BY owner, object_type
	/

=====

set pagesize 1000  LINESIZE 300

col COMP_ID format A9

col COMP_NAME format A40

col STATUS format A15

col VERSION format A12

col owner format a30

col object_name format a30

col object_type format a30

col COMMENTS for a60

col ACTION_TIME for a30

col VERSION for a25

--SPOOL DB_DD_CHECK.TXT

prompt /* Database identification */

select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;

select * from V$version where banner like 'Oracle Database%';

select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;

prompt  /* Database Component Registry status */

select comp_id, comp_name, status, version from dba_registry;

prompt  /* Level of patch in the database */

select * from dba_registry_history;

prompt /* INVALID objects count, by type -in detail */

select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';

select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;

select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;