Irrelevant thoughts of an oracle DBA

10 January 2009

How to use the plan_table table to sabotage your oracle upgrade

Filed under: bugs,infrastructure,upgrades / migrations — Freek D'Hooge @ 12:55

Lets say you need to upgrade your 9i database to 10g ( 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;

-------- -----------------------------------------------------------------
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?


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 or to

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)



  1. How do you avoid this prior to an Oracle Oracle doesn’t really support the removal of both sys.plan_table$ and sys.plan_table prior to the upgrade. I would go with this action just to avoid bug 6893419.

    What is your opinion?
    Thank you.

    Comment by Marie — 16 January 2009 @ 18:06 | Reply

    • Marie,

      Can you give more details about your situation?

      If you still are on 9i, then there should be no problem in deleting the sys.plan_table table (or synonym) before you move the database to the 10g home.
      If you have already moved the db to the new 10g home, then you would indeed encounter problems in dropping the table while in upgrade mode (at least, it did not work for me when I retested what happened).
      In that case you would run the upgrade and then follow the steps listed in the article to fix the isssue.


      Comment by dhoogfr — 16 January 2009 @ 18:16 | Reply

  2. We upgraded from 9.2 to The tables were created in 9.2. Now at this bug surfaces where no errors existed during the upgrade. What if the table was dropped then recreated in prior to the upgrade? Do you think this would avoid the bug?

    Thanks for all.

    Comment by Marie — 16 January 2009 @ 18:30 | Reply

  3. According to the metalink notes the problem only exists in and
    If you are currently on and have a plan_table in you sys schema, dropping the table before the upgrade to should be enough to avoid the problem.

    In you would then have the plan_table$ (as a temporary table) with a public synonym plan_table pointing to it.

    Comment by dhoogfr — 16 January 2009 @ 19:09 | Reply

    • Marie,

      I have read metalink note 565600.1 again and according this note the problem only exists when upgrading pre 10g databases to (or
      But, when I read further, it states that the problem occurs because the existing plan_table (which takes precedence over the public synonym to the plan_table$ table) does not contain the other_xml column, which is expected by the dbms_sqlpa package.
      This other_xml column was added in, so I would think that if you have a sys.plan_table table that was created before 10.2 and you upgrade to, you will encounter the problem.

      Starting with 10.1, which introduced the plan_table$ table with the public plan_table synonym, there is actually no need anymore to keep your own plan_table table.
      So my advice would be to drop any plan_table tables you would have and just use the plan_table$ table (through the public plan_table synonym).

      Comment by dhoogfr — 18 January 2009 @ 1:12 | Reply

  4. I’m wondering why you did not include run of the utlxplan.sql to recreate sys.plan_table. Wouldn’t there be a problem with plan_table$.

    Thanks much.

    Comment by Marie — 19 January 2009 @ 16:47 | Reply

  5. The sys.plan_table is not necessary either – have just tested this. All users, including the SYS user can use the temporary plan_table$. Thank you for your explanation. If my understanding is not correct, please let me know.

    Comment by Marie — 19 January 2009 @ 17:22 | Reply

  6. Marie,

    Your understanding is correct.
    The plan_table$ is replacing the plan_table through the public synonym, so there is no need anymore to create a separate plan_table.

    Comment by dhoogfr — 19 January 2009 @ 21:56 | Reply

  7. Upgrading from to

    got the error during catupgrd
    shutdown immediate;
    ran the following as originally suggested


    drop table sys.plan_table;

    shutdown immediate
    startup upgrade

    rerun catupgrd.sql
    Database Server is now valid.
    worked a treat – thanks

    Comment by Rob — 21 January 2009 @ 14:08 | Reply

  8. Rob,

    Glad to here the post helped you solve your problem.
    You did not need to run the catupgrd.sql script again. The prior scripts are enough to fix the errors.
    Although it could be that you need to run utlrp.sql before the status of the oracle “database server” component in the dba_registry is set to “valid”.

    Comment by dhoogfr — 23 January 2009 @ 0:11 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at

%d bloggers like this: