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 0

select 

'alter '

|| decode(object_type, 

'PACKAGE BODY','PACKAGE',

'TYPE BODY','TYPE',

object_type

) || ' '

|| owner || '.' || '"' || object_name || '"'  || ' compile '

|| decode(object_type, 

'PACKAGE BODY','body;',

'TYPE BODY','body;',

';'

)

,'show error ' 

|| object_type || ' '

|| owner || '.' 

|| object_name 

from dba_objects

where object_type in ('FUNCTION','PACKAGE','PROCEDURE','VIEW','TRIGGER','PACKAGE BODY','TYPE','TYPE BODY','INDEXTYPE')

and status = 'INVALID'

--and owner not in ('SYS','SYSTEM')

order by owner,

decode(object_type,

'VIEW', 1,

'PACKAGE', 2,

'PACKAGE BODY', 3,

'PROCEDURE', 4,

'FUNCTION', 5,

'TRIGGER', 6,

9

)

/

===

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;

====

spool more_info.txt
set echo on;
connect / as sysdba
@?/rdbms/admin/utlrp.sql
set pagesize 1000 linesize 200
col host_name format a30
col comp_name format a36
col version format a14
col status format a10
col action_time format a30
col action format a20
col comments format a45
col owner format a12
col object_name format a35
col object_type format a20
col name format a35
col table_name format a35
col column_name format a25
col index_name format a25
col grantee format a12
col grantor format a12
col privilege format a20
col schema_url format a60
col user format a12

col TEXT for a75
-- Instance and platform
select sysdate from dual;
select instance_name, host_name, version from v$instance;
select platform_name from v$database;
show con_name
-- Status of database components
select comp_name, version, status
from dba_registry
order by status, comp_name;
-- Registry history
select action_time, action, version, comments
from registry$history
order by 1 desc;
-- Invalid objects
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB','MDSYS','ORDSYS','CTXSYS','PUBLIC','CTXSYS')
order by owner, object_name;
-- Object errors
select owner, name, type, text
from dba_errors
where owner in ('SYS', 'XDB','MDSYS','ORDSYS','PUBLIC','CTXSYS')
order by owner, name;
-- Duplicate objects
select owner, object_name, object_type, status
from dba_objects
where object_name in
( select object_name
from dba_objects
where owner = 'XDB' )
and owner not in ('SYS', 'XDB','MDSYS','ORDSYS','PUBLIC','CTXSYS');
-- Privileges
select owner, table_name, grantee, privilege
from dba_tab_privs
where table_name in
( 'DBMS_JOB', 'DBMS_LOB', 'DBMS_SQL',
' DBMS_STATS', 'DBMS_RANDOM', 'UTL_FILE',
'UTL_HTTP', 'UTL_RAW', 'UTL_TCP' )
and grantee = 'PUBLIC'
order by table_name;
select grantee, privilege, table_name, grantor
from dba_tab_privs
where grantor = 'SYS' and grantee = 'XDB'
order by table_name;
-- XMLType tables
select owner, storage_type, count(*) "TOTAL"
from dba_xml_tables
group by owner, storage_type
order by owner, storage_type;
select owner, table_name, storage_type
from dba_xml_tables
where storage_type in ('OBJECT-RELATIONAL', 'BINARY')
and owner != 'XDB'
order by storage_type, table_name;
-- XMLType columns
select owner, storage_type, count(*) "TOTAL"
from dba_xml_tab_cols
group by owner, storage_type
order by owner, storage_type;
select owner, table_name, column_name
from dba_xml_tab_cols
where storage_type = 'BINARY'
and owner not in ('MDSYS', 'ORDDATA', 'SYS', 'XDB')
order by owner, table_name;
-- XDB Indexes
select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_name in ('XDBHI_IDX', 'XDB$RESOURCE_OID_INDEX');
-- XML indexes
select index_owner, index_name, parameters, table_owner, table_name, type, index_type
from dba_xml_indexes;
-- Registered schemas
select owner, count(*) "TOTAL"
from dba_xml_schemas
group by owner;
select owner, schema_url
from dba_xml_schemas
order by 1,2;
-- Resources in the XML repository
select distinct (a.username) "USER", count (r.xmldata) "TOTAL"
from dba_users a, xdb.xdb$resource r
where sys_op_rawtonum (extractvalue (value(r),'/Resource/OwnerID/text()')) = a.USER_ID
group by a.username;
select any_path from resource_view;
-- Any XDB object outside XDB?
SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME in
(Select OBJECT_NAME from DBA_OBJECTS where owner='XDB') and owner not in ('XDB','PUBLIC');
-- Is Spatial being used?
select owner, index_name
from dba_indexes
where ityp_name = 'SPATIAL_INDEX';
select owner, table_name, column_name
from dba_tab_columns
where data_type= 'SDO_GEOMETRY'
and owner != 'MDSYS';
-- Is Multimedia being used?
set serveroutput on;
@?/ord/im/admin/imremchk.sql
spool off

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

export PATH=$PATH:$ORACLE_HOME/bin
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql