How to use the plan_table table to sabotage your oracle upgrade

Lets say you need to upgrade your 9i database to 10g (10.2.0.4 to be exact), but you actually want to sabotage the upgrade (don’t know why, just assume you do).
Granted, there are many ways to do this, but you want to do it subtle. What are your options then?
Well, one option is to create the plan_table table in your sys schema (or a synonym plan_table to a plan table in another schema if you want to make it really subtle) before the upgrade.
If you do this, you will see the following message in your upgrade log:

Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY DBMS_SQLPA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
113/5    PL/SQL: SQL Statement ignored
118/44   PL/SQL: ORA-00904: "OTHER_XML": invalid identifier

And the “oracle database server” component in the dba_registry will be marked as invalid.
Mission accomplished I would say.

What is that?
You regret your actions and you want to fix the problem?

*sigh*

Ok then, to fix it you can use the following steps:

• drop the sys.plan_table table
• drop the sys.plan_table$ table
• drop all sys synonyms and public synonyms to the plan_table or the plan_table$
• @?/rdbms/admin/catplan.sql — recreate the plan table
• @?/rdbms/admin/dbmsxpln.sql — reload dbms_xplan spec
• @?/rdbms/admin/prvtxpln.plb — reload dbms_xplan implementation
• @?/rdbms/admin/prvtspao.plb — reload dbms_sqlpa

For those seeking more information:

Metalink note 565600.1 – ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
Metalink note 605317.1 – DBMS_SQLPA ORA-00904 OTHER_XML invalid identifier

According to the notes, the problem only exists with upgrades to 10.2.0.4 or to 11.1.0.7.

ps. Don’t ask me why I had a synonym called plan_table in my sys schema. I didn’t do it.
pps. This is why you should test your migration (I’m glad I did)