[SOLVED] Invalid objects after upgrade to Oracle 9.2.0.8 from 9.2.0.6
Note :
please use the commands carefully before executing in your server
its advised to test in test server.
Note :
Implementing or trying any of the advice from this blog is at your own risk.
Problem : Application VIEWs were invalid after upgrade to Oracle 9.2.0.8 from 9.2.0.6
=======
SELECT COUNT(*),OWNER FROM DBA_OBJECTS WHERE STATUS LIKE 'INVALID' GROUP BY OWNER;
COUNT(*) OWNER
---------- ------------------------------
40 APPUSER
Tried Methods : Executed utlrp multiple time, Manually Recomplied, Requested the view owner to recompile
=================
SELECT COUNT(*),OWNER FROM DBA_OBJECTS WHERE STATUS LIKE 'INVALID' GROUP BY OWNER;
COUNT(*) OWNER
---------- ------------------------------
40 APPUSER
35 VIEWS and 5 PACKAGE BODY were invalid
Tried to make it valid by executing UTLRP but din't work
@?/rdbms/admin/utlrp
35 VIEWS and 5 PACKAGE BODY were invalid
Tried recompile from SYS User manually one by one but din't work
SET SERVEROUTPUT ON;
ALTER VIEW APPUSER.TABLE_APP1 COMPLILE;
SHOW ERRORS
NO ERRORS
.
.
Did same for multiple view
But still its no objects were made VALID
Queried DBA_ERRORS
SELECT OWNER,NAME,TYPE,SEQUENCE,LINE,POSITION,TEXT FROM DBA_ERRORS;
OWNER NAME TYPE SEQUENCE LINE POSITION TEXT
-----------------------------------------------------------------------------------------------------------------------
APPUSER TABLE_APP1 VIEW 1 0 0 ORA-00942 table or view does not exist
.
.
.
40 rows selected.
Got error Table view doesn't exists
Saw from DBMS.METADATA.GETDDL that view were created or were showing without <SCHEMA_NAME>.<VIEW_NAME>
When tried to compile from SYS usere it was throwing ORA-00942 table or view does not exist in DBA_ERRORS
Solution
=========
Requested APPUSER to execute recompile the view from APPUSER it become VALID.
Note :
please use the commands carefully before executing in your server
its advised to test in test server.
Note :
Implementing or trying any of the advice from this blog is at your own risk.
Problem : Application VIEWs were invalid after upgrade to Oracle 9.2.0.8 from 9.2.0.6
=======
SELECT COUNT(*),OWNER FROM DBA_OBJECTS WHERE STATUS LIKE 'INVALID' GROUP BY OWNER;
COUNT(*) OWNER
---------- ------------------------------
40 APPUSER
Tried Methods : Executed utlrp multiple time, Manually Recomplied, Requested the view owner to recompile
=================
SELECT COUNT(*),OWNER FROM DBA_OBJECTS WHERE STATUS LIKE 'INVALID' GROUP BY OWNER;
COUNT(*) OWNER
---------- ------------------------------
40 APPUSER
35 VIEWS and 5 PACKAGE BODY were invalid
Tried to make it valid by executing UTLRP but din't work
@?/rdbms/admin/utlrp
35 VIEWS and 5 PACKAGE BODY were invalid
Tried recompile from SYS User manually one by one but din't work
SET SERVEROUTPUT ON;
ALTER VIEW APPUSER.TABLE_APP1 COMPLILE;
SHOW ERRORS
NO ERRORS
.
.
Did same for multiple view
But still its no objects were made VALID
Queried DBA_ERRORS
SELECT OWNER,NAME,TYPE,SEQUENCE,LINE,POSITION,TEXT FROM DBA_ERRORS;
OWNER NAME TYPE SEQUENCE LINE POSITION TEXT
-----------------------------------------------------------------------------------------------------------------------
APPUSER TABLE_APP1 VIEW 1 0 0 ORA-00942 table or view does not exist
.
.
.
40 rows selected.
Got error Table view doesn't exists
Saw from DBMS.METADATA.GETDDL that view were created or were showing without <SCHEMA_NAME>.<VIEW_NAME>
When tried to compile from SYS usere it was throwing ORA-00942 table or view does not exist in DBA_ERRORS
Solution
=========
Requested APPUSER to execute recompile the view from APPUSER it become VALID.
