-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathcount_invalid.sql
16 lines (16 loc) · 980 Bytes
/
count_invalid.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
col owner format a30
SELECT owner
, TO_CHAR(SUM(DECODE(object_type, 'PROCEDURE' , 1, 0)), '9G999G990') AS "PR"
, TO_CHAR(SUM(DECODE(object_type, 'FUNCTION' , 1, 0)), '9G999G990') AS "FN"
, TO_CHAR(SUM(DECODE(object_type, 'TRIGGER' , 1, 0)), '9G999G990') AS "TRG"
, TO_CHAR(SUM(DECODE(object_type, 'PACKAGE' , 1, 0)), '9G999G990') AS "PKG"
, TO_CHAR(SUM(DECODE(object_type, 'PACKAGE BODY', 1, 0)), '9G999G990') AS "PKG BODY"
, TO_CHAR(SUM(DECODE(object_type, 'TYPE' , 1, 0)), '9G999G990') AS "TYPE"
, TO_CHAR(SUM(DECODE(object_type, 'TYPE BODY' , 1, 0)), '9G999G990') AS "TYPE BODY"
, TO_CHAR(SUM(DECODE(object_type, 'SYNONYM' , 1, 0)), '9G999G990') AS "SYN"
, TO_CHAR(SUM(DECODE(object_type, 'VIEW' , 1, 0)), '9G999G990') AS "VIEW"
, TO_CHAR(SUM(DECODE(object_type, 'SEQUENCE' , 1, 0)), '9G999G990') AS "SEQ"
FROM all_objects
WHERE status = 'INVALID'
GROUP BY ROLLUP(owner);
col owner clear