【oracle】用sql获取状态为异常的存储过程

问题：
nested exception is java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00905: object
XXX is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored

原因：
同事写的存储过程创建脚本有问题
BEGIN
EXECUTE IMMEDIATE ‘ALTER TABLE ’ || tableName || ’ ADD PARTITION ’ || name || ’ values (’ || month || ‘)’; //NOSONAR
END;
多加了 //NOSONAR，导致存储过程无效。

通过sql语句查看全部无效存储过程：
select * from all_objects where object_type=‘PROCEDURE’ AND OWNER = ‘XXX’ AND status <> ‘VALID’;
select * from dba_objects where object_type=‘PROCEDURE’ AND OWNER = ‘XXX’ AND status <> ‘VALID’;
select * from user_objects where object_type=‘PROCEDURE’ AND OWNER = ‘XXX’ AND status <> ‘VALID’;

其中all，dba，user存在使用场景区别