ORA-04063: table/view has errors

I got ” ORA-04063: table/view has errors ”  error in Oracle database.

 

ORA-04063: table/view has errors

 

Details of error are as follows.

ORA-04063: table/view has errors

Cause: Attempt to execute a stored procedure or use a view that has errors. For stored 
procedures, the problem could be syntax errors or references to other, non-existent procedures.
 For views, the problem could be a reference in the view's defining query to a non-existent 
table. Can also be a table which has references to non-existent or inaccessible types.

Action: Fix the errors and/or create referenced objects as necessary.


DBMS STATS package is failing with the errors below:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4285/5 PL/SQL: Statement ignored
4285/27 PLS-00302: component 'IS_STATS_FROM_UPGRADE' must be declared
SQL> @?/rdbms/admin/utlrp.sql
SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06512: at "SYS.UTL_RECOMP", line 899
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at "SYS.UTL_RECOMP", line 260
ORA-06512: at "SYS.UTL_RECOMP", line 803
ORA-06512: at line 4

 

 

table/view has errors

This ORA-04063 errors are related with the Attempt to execute a stored procedure or use a view that has errors. For stored procedures, the problem could be syntax errors or references to other, non-existent procedures.

For views, the problem could be a reference in the view’s defining query to a non-existent table. Can also be a table which has references to non-existent or inaccessible types.

Fix the errors and/or create referenced objects as necessary.

 

In this case Package body for SYS.DBMS_STATS is invalid.

 

Compile the package body as follows.

SQL > alter package body PACKAGE_NAME compile;

 

 

Packages and Types can be validated by running catalog and catproc scripts:

$sqlplus "/as sysdba"
SQL> startup restrict
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;

Shutdown your database in the normal mode and startup with the normal mode.

 

If you got this error for procedure and function, you can compile it as follows.

ALTER PROCEDURE | FUNCTION|  PACKAGE [<schema>.] <name> COMPILE  [BODY]

 

You can compile all invalid objects running the utlrp.sql as follows.

 

SQL > @?/rdbms/admin/utlrp.sql

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

See more