Oracle----升级数据库时碰到 "plan_table" is old version

SQL> select object_name,object_type,owner from dba_objects where object_name like '%PLAN_TABLE%'

OBJECT_NAME OBJECT_TYPE OWNER
------------------------------ ------------------- ------------------------------
PLAN_TABLE SYNONYM OFFLINE_AAMS
PLAN_TABLE SYNONYM OFFLINE_DFS_U
PLAN_TABLE TABLE SYS
PLAN_TABLE TABLE OFFLINE_EMS31
PLAN_TABLE TABLE OFFLINE_DEC1
PLAN_TABLE SYNONYM OFFLINE_PUB
PLAN_TABLE TABLE OFFLINE_BWS
PLAN_TABLE TABLE OFFLINE_MNL
PLAN_TABLE TABLE OFFLINE_DFS
PLAN_TABLE SYNONYM OFFLINE_DFS_Q
PLAN_TABLE1 TABLE OFFLINE_DEC
PLAN_TABLE TABLE OFFLINE_DEC
PLAN_TABLE SYNONYM PUBLIC
PLAN_TABLE SYNONYM OFFLINE_BWS_Q
PLAN_TABLE SYNONYM OFFLINE_MNL_Q
PLAN_TABLE1 SYNONYM OFFLINE_DEC_Q
PLAN_TABLE SYNONYM OFFLINE_DEC_Q
PLAN_TABLE SYNONYM OFFLINE_BWS_U
PLAN_TABLE SYNONYM OFFLINE_MNL_U
PLAN_TABLE1 SYNONYM OFFLINE_DEC_U
PLAN_TABLE SYNONYM OFFLINE_DEC_U
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
PLAN_TABLE$ TABLE SYS

24 rows selected.

SQL> col synonym_name for a15
SQL> col owner for a15
SQL> col table_owner for a20
SQL> col table_name for a25
SQL> select synonym_name,owner,table_owner,table_name
from dba_synonyms where synonym_name='PLAN_TABLE'

SYNONYM_NAME OWNER TABLE_OWNER TABLE_NAME
--------------- --------------- -------------------- -------------------------
PLAN_TABLE PUBLIC SYS PLAN_TABLE$
PLAN_TABLE OFFLINE_AAMS OFFLINE_DEC PLAN_TABLE
PLAN_TABLE OFFLINE_PUB OFFLINE_PTS PLAN_TABLE
PLAN_TABLE OFFLINE_BWS_Q OFFLINE_BWS PLAN_TABLE
PLAN_TABLE OFFLINE_DEC_Q OFFLINE_DEC PLAN_TABLE
PLAN_TABLE OFFLINE_MNL_Q OFFLINE_MNL PLAN_TABLE
PLAN_TABLE OFFLINE_BWS_U OFFLINE_BWS PLAN_TABLE
PLAN_TABLE OFFLINE_DEC_U OFFLINE_DEC PLAN_TABLE
PLAN_TABLE OFFLINE_MNL_U OFFLINE_MNL PLAN_TABLE
PLAN_TABLE OFFLINE_DFS_U OFFLINE_DFS PLAN_TABLE
PLAN_TABLE OFFLINE_DFS_Q OFFLINE_DFS PLAN_TABLE

11 rows selected.

SQL> select object_id,session_id,process,locked_mode from v$locked_object;

OBJECT_ID SESSION_ID PROCESS LOCKED_MODE
---------- ---------- ------------ -----------
9090 5368 295018 3
9090 5386 381058 3

SQL> select object_name,object_type,owner from dba_objects where object_id='9090';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE OWNER
------------------- ---------------
PLAN_TABLE
TABLE SYS

SQL> select sid,serial#,username,program,status from v$session where sid='5368';

SID SERIAL# USERNAME
---------- ---------- ------------------------------
PROGRAM STATUS
------------------------------------------------ --------
5368 6671 SYS
sqlplus@db1p590(TNS V1-V3) INACTIVE

SQL> alter system kill session '5368,6671';

System altered.

drop table plan_table$;
drop table plan_table;

@catplan.sql --recreate the plan table
@dbmsxpln.sql --reload dbms_xplan spec
@prvtxpln.plb --reload dbms_xplan implementation
@prvtspao.plb --reload dbms_sqlpa

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26078027/viewspace-1053191/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26078027/viewspace-1053191/