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@]