How to fetch the list of errors for invalid objects in Oracle 10g

Question

Explanation: I have more than 200 invalid objects in my DB, the reasons could be couple of objects only (others due to dependancy). Is there a way we can select the object name and the 'Error Reason' for it being invalid.

Answer 1

You could query [DBA/ALL/USER]_ERRORS. It describes current errors on all stored objects (views, procedures, functions, packages, and package bodies) owned by the current user.

Chose which view to query, depending on the privileges you have:

• DBA_ : All objects in the database
• ALL_ : All objects owned by the user and on which the user has been granted privileges
• USER_ : All objects owned by the user

For example,

I create a procedure with a compilation error, and I want to query the error details:

SQL> CREATE OR REPLACE PROCEDURE p
  2  BEGIN
  3  NULL
  4  END;
  5  /

Warning: Procedure created with compilation errors.

SQL>
SQL> SELECT NAME, TYPE, line, text FROM user_errors;

NAME  TYPE             LINE TEXT
----- ---------- ---------- --------------------------------------------------
P     PROCEDURE           2 PLS-00103: Encountered the symbol "BEGIN" when exp
                            ecting one of the following:

                               ( ; is with authid as cluster compress order us
                            ing compiled
                               wrapped external deterministic parallel_enable
                            pipelined
                               result_cache accessible


SQL>

Read more about it in documentation here

Answer 2

You can check with this view:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
FROM ALL_OBJECTS
WHERE STATUS <> 'VALID';

resp. USER_OBJECTS or DBA_OBJECTS.

ALL_ERRORS does not show all invalid objects.

Example:

CREATE TABLE tt (a NUMBER);
CREATE OR REPLACE VIEW ttt AS SELECT * FROM tt;
DROP TABLE tt;

SELECT * FROM USER_ERRORS;

no rows selected.

However, after selecting the view once you get an entry:

SELECT * FROM ttt;

Error at line 1
ORA-04063: view "xxxx.TTT" has errors

SELECT NAME, TYPE, TEXT FROM USER_ERRORS;

NAME    TYPE    TEXT
--------------------------------------------------------
TTT     VIEW    ORA-00942: table or view does not exist