Friday, March 08, 2013

Invalid database objects on apex.oracle.com?

Most people are aware of apex.oracle.com, the demonstration instance of Oracle Application Express.  It's primarily used for people to "kick the tires" of APEX.  Last week alone, there were 810 new workspaces approved on this instance.

Changes are coming to apex.oracle.com - it's going to be consolidated onto other hardware and also Oracle Database Vault will be installed and implemented.  Part of the process of installing Oracle Database Vault includes running the script $ORACLE_HOME/rdbms/admin/utlrp.sql.  This script will attempt to recompile all invalid objects in the database.

As of today, 08-MAR-2013, there are 62,197 invalid database objects across all of the workspaces on apex.oracle.com - not that bad, considering there are 17,447 APEX workspaces and 17,984 workspace schemas.  However, it takes a long time to attempt to recompile 62,197 invalid database objects.  And this will only serve to dramatically increase the outage time for this instance.

Thus:

  1. A listing of all invalid database objects in the workspace schemas was computed today, 08-MAR-2013.
  2. A second listing of all invalid database objects in the workspace schemas will be computed again on 13-MAR-2013.
  3. All invalid objects contained in both reports will be immediately dropped with no chance of recovery (as the Recycle Bin will be purged).

Update March 12, 2013:  This drop and purge will no longer occur on 13-MAR-2013, nor at any time in the future.  We were able to make significant progress in the reduction in the number of invalid objects (reduced by 85%).  Thus, we're going to live with the additional outage time cause by this recompilation instead of dropping objects in workspace schemas.

2 comments:

David Peake said...

A simple query you can run in SQL Commands to list the invalid objects:

select object_name, object_type, created, last_ddl_time from user_objects
where status = 'INVALID'

Niall Mc Phillips said...

Thanks for the warning Joel.
All my objects are compiled now.