Today I was trying to see if upgrading from 11.2.0.2 to 11.2.0.4 would change the SYS.WRH$_SQL_PLAN table. This table is large on our production system so I wanted to find out if some time-consuming update to this table would occur that would slow down our production upgrade but not be detected on our test systems. We recently performed this upgrade on our development database and I was looking at the logs to see whether SYS.WRH$_SQL_PLAN was modified. I found this curious entry (edited for brevity):
create table WRH$_SQL_PLAN 2 (snap_id number /* last snap id, used for purging */ 3 ,dbid number not null 4 ,sql_id varchar2(13) not null ... 42 using index tablespace SYSAUX 43 ) tablespace SYSAUX 44 / Table created.
The “Table created.” message sounds like the database created a new table without any errors. But, looking at DBA_OBJECTS the table was not new. So, I guessed that when you are running the catproc.sql script which includes the create table statement for SYS.WRH$_SQL_PLAN it must contain something that suppresses the error that you should get when you try to create a table and the table already exists:
ORA-00955: name is already used by an existing object
So, I opened my 11.2.0.3 test database using STARTUP RESTRICT and ran @catproc.sql as SYSDBA and to my surprise I got the error just as you normally would:
42 using index tablespace SYSAUX 43 ) tablespace SYSAUX 44 / create table WRH$_SQL_PLAN * ERROR at line 1: ORA-00955: name is already used by an existing object
So, I decided to restart this database with STARTUP UPGRADE and rerun catproc.sql and as a result the error disappeared:
40 ,constraint WRH$_SQL_PLAN_PK primary key 41 (dbid, sql_id, plan_hash_value, id) 42 using index tablespace SYSAUX 43 ) tablespace SYSAUX 44 / Table created.
Cue the mysterious Twilight Zone music…
I guess this is a “feature” of the startup upgrade command but the “Table created.” message is kind of confusing. The table isn’t really created if it exists. But, I guess the good thing is that it doesn’t report an error.
– Bobby
