Invalid Objects

Databases

Oracle

Oracle Diagnostics & Tuning

Check

For a count of invalid objects...

@/u01/dba/dbs/invobjcnt.sql

SELECT COUNT(*)

FROM dba_objects

WHERE status != 'VALID';

For a count of invalid objects by owner...

@/u01/dba/dbs/invobjowncnt.sql

SELECT owner, COUNT(*)

FROM dba_objects

WHERE status != 'VALID'

GROUP BY owner

ORDER BY owner;

For a list of invalid objects for a specified owner...

@/u01/dba/dbs/invobjown.sql owner

SELECT object_type, object_name

FROM dba_objects

WHERE owner = '&1'

AND status != 'VALID';

For a list of all invalid objects sorted by owner...

@/u01/dba/dbs/invobj.sql

SET LINESIZE 150

COLUMN OBJECT_NAME FORMAT A50

SELECT owner, object_type, object_name

FROM dba_objects

WHERE status != 'VALID'

ORDER BY owner, object_type, object_name;

After an upgrade you can use this script to compare invalid objects before and after the upgrade...

@$ORACLE_HOME/rdbms/admin/utluiobj.sql

Fix

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

@utlrp.sql

exit

If invalid objects remain, further investigation will be required.

Troubleshooting

DBMS_ASH_INTERNAL

If SYS.DBMS_ASH_INTERNAL is invalid then you may be running into bug 27938623

See: 2392962.1 - After Application of April PSU Patch 27338041(12.1.0.2.180417) , DBMS_ASH_INTERNAL Is Invalid.

Workaround

cp /u01/img/PSU-APR-2018/27726471/27338041/files/rdbms/admin/prvtash.plb $ORACLE_HOME/rdbms/admin

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

@prvtash.plb

V$OLAP_ALTER_SESSION

If V$OLAP_ALTER_SESSION is invalid...

See: 2120583.1 - 12c: VIEW V$OLAP_ALTER_SESSION IS IN INVALID STATUS AFTER UPGRADING DATABASE

Workaround

sqlplus / as sysdba

drop public synonym ALL_OLAP_ALTER_SESSION;

drop view sys.V$OLAP_ALTER_SESSION;