List invalid objects
SQL> @invaln
Show invalid objects
[= allowed ] Schema (or null) :
OWNER OBJECT_NAME OBJECT_TYPE CREATED TIMESTAMP ERROR COUNT
------------------------------ ------------------------------ ------------------- ------------------- ------------------- -----------
FOUR3_RTREEA LOAD_ROOM_LINE_FYI PROCEDURE 15/09/2015 09:35:23 2015-09-23:10:14:35 6
FOUR3_RTREEB LOAD_ROOM_LINE_FYI PROCEDURE 24/08/2015 09:57:24 2015-09-23:10:14:35 6
ABD_TOOLS THIY_ADMIN_TOOL PACKAGE 23/10/2012 07:56:17 2015-07-31:10:40:34 0
ABD_TOOLS THIY_ADMIN_TOOL PACKAGE BODY 23/10/2012 08:00:38 2015-07-31:10:40:34 0
CCS_READ_USERB SEVENIL_BARREL_ROUTES SYNONYM 23/10/2012 07:22:36 2015-04-08:11:13:09 0
CCS_READ_USERB SEVENIL_TIME_LIMITS SYNONYM 23/10/2012 07:22:36 2015-04-08:11:13:26 0
CCS_UPDATE_USERB SEVENIL_BARREL_ROUTES SYNONYM 23/10/2012 07:22:30 2015-04-08:11:13:40 0
CCS_UPDATE_USERB SEVENIL_TIME_LIMITS SYNONYM 23/10/2012 07:22:30 2015-04-08:11:13:54 0
FF_KLT DM_VIEW VIEW 13/02/2015 13:02:03 2015-02-13:13:24:12 0
FF_KLT_RO FH_ROOM_HELD8_PRICE_GEN SYNONYM 30/03/2015 12:26:59 2015-09-23:10:14:37 0
FF_KLT_RO FH_ROOM_HELD8_PRICE SYNONYM 30/03/2015 12:26:59 2015-09-23:10:14:37 0
FF_KLT_RO FH_FLIGHT_SEQ_GEN SYNONYM 30/03/2015 12:27:00 2015-09-23:10:14:37 0
FF_KLT_RO FH_PACKAGE_SUMMARY_SOLR SYNONYM 30/03/2015 13:19:43 2015-09-23:10:14:38 0
SOLR_HOTELPACKAGES_USER FH_ROOM_HELD8_PRICE_GEN SYNONYM 30/03/2015 12:15:50 2015-09-23:10:14:38 0
SOLR_HOTELPACKAGES_USER FH_ROOM_HELD8_PRICE SYNONYM 30/03/2015 12:15:55 2015-09-23:10:14:38 0
SOLR_HOTELPACKAGES_USER FH_FLIGHT_SEQ_GEN SYNONYM 30/03/2015 12:15:59 2015-09-23:10:14:38 0
SUPPORT SUSH_PURGE PROCEDURE 18/05/2015 16:38:31 2015-09-23:10:14:36 2
TSREPORTING FNEXPIRATIONDATECONDITION FUNCTION 13/04/2012 15:38:09 2015-09-23:10:14:33 3
RTREEA GOLDMINE_HOUSEKEEPING PACKAGE BODY 15/09/2015 09:40:28 2015-09-23:10:14:34 10
RTREEB GOLDMINE_HOUSEKEEPING PACKAGE BODY 24/08/2015 10:06:23 2015-09-23:10:14:35 10
RTREE_USERA LOAD_ROOM_LINE_FYI SYNONYM 15/09/2015 09:43:15 2015-09-23:10:14:38 0
RTREE_USERB LOAD_ROOM_LINE_FYI SYNONYM 24/08/2015 10:10:31 2015-09-23:10:14:39 0
22 rows selected.
col object_name for a30
prompt
prompt Show invalid objects
prompt
spool invaln.rpt
accept x_owner prompt "[= allowed ] Schema (or null) : "
select a.owner, a.object_name, a.object_type, a.created, a.timestamp, count( b.owner ) "Error Count"
from dba_objects a,
dba_errors b
where ( '&x_owner' is null or
( ( substr( upper( nvl( '&&x_owner', 'x' ) ), 1, 1 ) <> '=' and a.owner like upper( '%&&x_owner%' ) )
or
( substr( upper( '&&x_owner' ), 1, 1 ) = '=' and a.owner = upper( substr( '&&x_owner', 2 ) ) )
)
)
and nvl( status, 'x' ) <> 'VALID'
and instr( a.object_name, '==' ) = 0
and b.owner (+)= a.owner
and b.name (+)= a.object_name
and b.type (+)= a.object_type
group by a.owner, a.object_name, a.object_type, a.created, a.timestamp
order by a.owner, a.object_name;
spool off