DUPLICATE OF SOL49941.
Running the Explain Plan on any query results in the following error:
ORA-00980: synonym translation is no longer valid.
TOAD_PLAN_TABLE is used for the Explain Plan command.
Toad Explain Plan table synonym is invalid because the Toad Explain Plan table was dropped at one point and its synonym still remains.
This error message is usually caused by a query trying to access invalid synonyms in your database that need to be dropped. Run the below query to find these synonyms:
select decode (s.owner,PUBLIC,PUBLIC SYNONYM ,
SYNONYM||s.owner||.)||s.synonym_name||;
from dba_synonyms s
where table_owner not in(SYSTEM,SYS)
and db_link is null
and not exists
(select 1
from dba_objects o
where s.table_owner=o.owner
and s.table_name=o.object_name);
RESOLUTION 1:
Re-install the Explain Plans Server Side Objects by using the Server Side Objects Wizard.
RESOLUTION 2:
Drop TOAD_PLAN_TABLE synonym and recreate the table and its synonym. Script provided below.
DROP PUBLIC SYNONYM TOAD_PLAN_TABLE;
CREATE TABLE TOAD_PLAN_TABLE
(
STATEMENT_ID VARCHAR2(32 CHAR),
TIMESTAMP DATE,
REMARKS VARCHAR2(80 CHAR),
OPERATION VARCHAR2(30 CHAR),
OPTIONS VARCHAR2(30 CHAR),
OBJECT_NODE VARCHAR2(128 CHAR),
OBJECT_OWNER VARCHAR2(30 CHAR),
OBJECT_NAME VARCHAR2(30 CHAR),
OBJECT_INSTANCE NUMBER,
OBJECT_TYPE VARCHAR2(30 CHAR),
SEARCH_COLUMNS NUMBER,
ID NUMBER,
COST NUMBER,
PARENT_ID NUMBER,
POSITION NUMBER,
CARDINALITY NUMBER,
OPTIMIZER VARCHAR2(255 CHAR),
BYTES NUMBER,
OTHER_TAG VARCHAR2(255 CHAR),
PARTITION_ID NUMBER,
PARTITION_START VARCHAR2(255 CHAR),
PARTITION_STOP VARCHAR2(255 CHAR),
DISTRIBUTION VARCHAR2(30 CHAR),
OTHER LONG
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX TPTBL_IDX ON TOAD_PLAN_TABLE
(STATEMENT_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE PUBLIC SYNONYM TOAD_PLAN_TABLE FOR TOAD_PLAN_TABLE;
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center