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.

Synonyms (Part 2)

A colleague had an ORA-01775 error so I ran some tests on Oracle 11.2 to see how this might happen. First I created a table:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL>

Then I created a public synonym for that table:

SQL> create public synonym andrew for andrew
  2  /

Synonym created.

SQL>

Next I dropped the table:

SQL> drop table andrew
  2  /

Table dropped.

SQL>

Then when I tried to use the public synonym, Oracle returned an ORA-01775:

SQL> select count(*) from andrew
  2  /
select count(*) from andrew
                     *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL>

But when I described it, I got a different error:

SQL> desc andrew
SP2-0749: Cannot resolve circular path of synonym "andrew"
SQL>

You get a similar problem if you create a public synonym for a non-existent table:

SQL> create public synonym joe for joe
  2  /

Synonym created.

SQL> desc joe
SP2-0749: Cannot resolve circular path of synonym "joe"
SQL>

You cannot do this with a private synonym as Oracle does not allow a private synonym to have the same name as the object to which it refers:

SQL> create table desmond (col1 number)

  2  /

Table created.

SQL> create synonym desmond for desmond

  2  /

create synonym desmond for desmond

*

ERROR at line 1:

ORA-01471: cannot create a synonym with same name as

object

SQL>

But, if you want to produce an ORA-01775 with private synonyms, you can do it like this:

SQL> create synonym andrew1 for andrew2
  2  /

Synonym created.

SQL> create synonym andrew2 for andrew1
  2  /

Synonym created.

SQL> select count(*) from andrew1
  2  /
select count(*) from andrew1
                     *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> desc andrew1
SP2-0749: Cannot resolve circular path of synonym "andrew1"
SQL>

Synonyms

Tested on an Oracle 11 database.

You can use a synonym to create an alias for an object e.g. a table or view:

 

SQL> create synonym a for b

  2  /

 

Synonym created.

 

SQL> create public synonym x for y

  2  /

 

Synonym created.

SQL>
  

If the object does not exist, you do not get an error until you try to access it:

SQL> desc a

ERROR:

ORA-04043: object "SYSTEM"."B" does not exist

 

SQL> desc x

ERROR:

ORA-04043: object "SYSTEM"."Y" does not exist

 

SQL>

And you can even create a synonym for an object which belongs to a user who does not exist:

SQL> create synonym c for d.e;

 

Synonym created.

 

SQL> select count(*) from dba_users

  2  where username = 'D';

 

  COUNT(*)

----------

         0

 

1 row selected.

 

SQL> desc c

ERROR:

ORA-04043: object "D"."E" does not exist

 

SQL>

You Cannot Analyze a Table Through a Synonym

This was tested on an Oracle 11 database. First I created a table and analyzed it: 

SQL> create table andrew1 (one_col number)

  2  /

Table created.

SQL> analyze table andrew1 compute statistics

  2  /

Table analyzed.

SQL>

Then I created a synonym for the table:

SQL> create synonym andrew2 for andrew1

  2  /

Synonym created.

SQL>

I found that I was able to describe the table via the synonym:

SQL> desc andrew2

 Name                    Null?    Type

 ----------------------- -------- ----------------

 ONE_COL                          NUMBER

SQL>

... but I could not analyze it:

SQL> analyze table andrew2 compute statistics

  2  /

analyze table andrew2 compute statistics

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>