Trying to solve ORA-00980: synonym translation is no longer valid
Oracle 9i, Standard Edition
I ran the catalog.sql script on my Enterprise DB in preparation for exporting a schema from my Oracle 9i version 9.2.0.1.0 Enterprise DB with an Enterprise Manager Server and Intelligent Agent to an Oracle 9i version 9.0.1.1.0 Personal Edition DB(Each DB running on separate PCs in my LAN). I ran the catalog.sql script per the following Oracle documentation under the section "Before using Export" at the very beginning of the documentation accessible at web address: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1006760. Ever since then, I'm unable to start the Management Server(Xp Profess displays a pop-up informing that Management Server Service has been started and stopped due no tasks to be performed). If I delete and try to recreate a repository for the Management Server, I get an "ORA-00980: synonym translation is no longer valid".I found out from further reading to export from newer to older version of Oracle DB you have to use the export utility of older version. If I attempt to see the tables in my 9i version 9.2.0.1.0 Enterprise Edition DB from the Oracle Navigator in 9i version 9.0.1.1.0 Personal Edition, I get the "ORA-00980: synonym translation is no longer valid".
I found the following SQL statement from an Oracle DBA who works for a consulting firm but received the error at the end of the copy&paste. I ran the following script in SQLPLUS logged in as SYSTEM and it comes back with error per the following copy&paste from SQLPLUS:
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jul 25 11:27:28 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
2 'SYNONYM'||s.owner||'.')||s.synonym_name||';'
3 from dba_synonyms s
4 where table_owner not in('SYSTEM','SYS')
5 and db_link is null
6 and not exists
7 (select 1
8 from dba_objects o
9 where s.table_owner=o.owner
10 and s.table_name=o.object_name);
from dba_synonyms s
*
ERROR at line 3:
ORA-00980: synonym translation is no longer valid
I get the error "ORA-00980: synonym translation is no longer valid" even when I do a simple "select * from dba_synonyms. dba_synonyms is a view based on a SQL statement.
Perplexed,
aascotty
0
