Saturday, April 14, 2012

ORA-04043 and ORA-00980

This example was tested on Oracle 9. First create a table:
 
SQL> create table table1 (col1 number)
  2  /
 
Table created.
 
SQL>
 
Then create a synonym pointing to that table:
 
SQL> create synonym synonym1 for table1
  2  /
 
Synonym created.
 
SQL> desc synonym1
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                NUMBER
 
SQL>
 
Rename the underlying the table:
 
SQL> rename table1 to table2
  2  /
 
Table renamed.
 
SQL>
 
Now, if you describe the table via the synonym, you get an ORA-04043, which I have shown elsewhere:
 
SQL> desc synonym1
ERROR:
ORA-04043: object "ORACLE"."TABLE1" does not exist
 
SQL>
 
But, if you select from the table via the synonym, you get an ORA-00980:
 
SQL> select * from synonym1
  2  /
select * from synonym1
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
 
SQL>
 
Depending on the circumstances, you could fix this as follows:
(1)  Rename the table back again, if it was renamed by mistake in the first place.
(2)  Drop and recreate the synonym so that it points to the new table name.

No comments: