Monitoring Invalid objects in Oracle EBS R12.2

ADZDUTLRECMP.sql : Compiling Invalid Objects during adop patching.

In R12.2, unlike earlier releases, stub objects are not displayed in the dba_objects table. Therefore, querying this table will not show all the invalid objects.

STUB: Stub objects are related to the editioning capabilities of the database.

The following query can be used to display the invalid objects:

select * from
(
select
eusr.user_name owner
, count(decode(obj.type#,88,NULL,decode(obj.status,1,NULL,1))) Actual
, count(decode(obj.type#,88,decode(obj.status,1,NULL,1),NULL)) Stub
, count(decode(obj.type#,88,decode(obj.status,1,NULL,1),decode(obj.status,1,NULL,1))) Total
from
sys.obj$ obj
, sys.obj$ bobj
, (
select
xusr.user#
, xusr.ext_username user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 2) xusr
, (select * from sys.obj$ where owner# = 0 and type# = 57) ed
where xusr.spare2 = ed.obj#
union
select
busr.user#
, busr.name user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 1 or user# = 1) busr
, (select * from sys.obj$ where owner# = 0 and type# = 57) ed
where ed.name = 'ORA$BASE'
) eusr
where obj.owner# = eusr.user#
and bobj.obj#(+) = obj.dataobj#
and obj.type# not in (0, 10)
and ( obj.type# <> 88 or (obj.type# = 88 and bobj.type# <> 10) )
and obj.remoteowner is null
group by eusr.user_name
) x
where total > 0
order by 1
/