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 ,
from dba_synonyms s
where table_owner not in(SYSTEM,SYS)
and db_link is null
and not exists
from dba_objects o
For older versions of Toad for Oracle i.e. 9.7.2, re-install the Explain Plans Server Side Objects by using the Server Side Objects Wizard.
For newer versions of Toad for Oracle i.e. 11.6.1, drop the TOAD_PLAN_TABLE “table” (usually owned by the TOAD user) and the TOAD_PLAN_TABLE “synonym” (owned by the PUBLIC user). Please drop both.
Here are the drop scripts:
DROP PUBLIC SYNONYM "TOAD_PLAN_TABLE";
DROP TABLE TOAD.TOAD_PLAN_TABLE CASCADE CONSTRAINTS PURGE;
The next time you run an Explain Plan, Toad will prompt a window asking to create the explain plan table. Enter TOAD as the plan table schema (recommended). Also check-mark “Grant to public and create public synonym”. Next, click on OK. The explain plan should now work.