Upgrade APEX to 4.0.1 encounters “ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists”

Few weeks back, we’ve upgraded multiple APEX installations (development, test and production) to new version 4.0.1 from the default version 3.0.1.00.12 in Oracle 11g R1. The upgrade (just running the apexins.sql) went well for all except one. We got a strange error during the last stage of installation.

Performing Application Express component validation - please wait...

Completing registration process. 23:02:26
declare
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 18566
ORA-06512: at "SYS.DBMS_STATS", line 19015
ORA-06512: at "SYS.DBMS_STATS", line 21374
ORA-06512: at "SYS.DBMS_STATS", line 21417
ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 1067
ORA-06512: at "SYS.DBMS_REGISTRY", line 578
ORA-06512: at line 7

The status of the APEX component in the DBA_REGISTRY is set to LOADED.

SYS@DB11G AS SYSDBA> select comp_name, version, status from dba_registry
where comp_name like '%Application Express';

COMP_NAME                             VERSION              STATUS
-----------------------------------   -------------------- -----------
Oracle Application Express            4.0.1.00.03          LOADED

Many attempts to uninstall (using apxremov.sql) and reinstall still encountered the same error.

Initial thought was with the version conflict. I even cleaned up the version coming with database by following the instructions from the MOS note ID 558340.1 – How to Uninstall Oracle HTML DB / Application Express from the Database?  This includes drop all APEX* and FLOWS* schemas as well as public synonyms.

All did not help.

Tracing from the installation script – apexins.sql, the installation portion calls the coreins.sql. All scripts within it run fine until the last one – endins.sql, which according to the description note in the script is to perform the component validation at the end of the installation. The only main operation in the endins.sql is a call to the procedure validate_apex which is owned by SYS. This is just to confirm that actually all installations went fine but failed only when validation.

So I’ve decided to run just it. Surprisingly, it came out with no errors. And more importantly, the APEX registry is now VALID 🙂

SYS@DB11G AS SYSDBA> exec sys.validate_apex;

PL/SQL procedure successfully completed.

SYS@DB11G AS SYSDBA> select comp_name, version, status from dba_registry
where comp_name like '%Application Express';

COMP_NAME                             VERSION STATUS
-----------------------------------   ------------------------------ -----------
Oracle Application Express            4.0.1.00.03                    VALID

I still don’t know why the validation failed during the installation. Interesting thing is that this issue happened only on one system.