ORA-01775: looping chain of synonyms

Question

I am creating a table (here below is the code) - this is executed through some script on unix. The script also creates some synonyms (not sure what/how):

drop table BNS_SAA_MESSAGES;
CREATE TABLE BNS_SAA_MESSAGES
(
HostNumber varchar(50) NOT NULL,
SAAMessage varchar(2048) NOT NULL,
PRIMARY KEY (HostNumber)
);

I'm getting the following error:

Processing bns_saa_messages
cat: cannot open bns_saa_messages.sql

Commit complete.

GRANT SELECT ON bns_saa_messages TO RL_ORDFX_RPT

GRANT SELECT ON bns_saa_messages TO RL_ORDFX_RPT
                *
ERROR at line 1:
ORA-01775: looping chain of synonyms


GRANT INSERT ON bns_saa_messages TO RL_ORDFX_RPT

GRANT INSERT ON bns_saa_messages TO RL_ORDFX_RPT
                *
ERROR at line 1:
ORA-01775: looping chain of synonyms


GRANT UPDATE ON bns_saa_messages TO RL_ORDFX_RPT

GRANT UPDATE ON bns_saa_messages TO RL_ORDFX_RPT
                *
ERROR at line 1:
ORA-01775: looping chain of synonyms


GRANT DELETE ON bns_saa_messages TO RL_ORDFX_RPT

GRANT DELETE ON bns_saa_messages TO RL_ORDFX_RPT
                *
ERROR at line 1:
ORA-01775: looping chain of synonyms


create public synonym bns_saa_messages for ORDMSO.bns_saa_messages

create public synonym bns_saa_messages for ORDMSO.bns_saa_messages
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object



Commit complete.

I googled for ORA-01775: looping chain of synonyms and it seems to mean that a something was removed but there is a pointer to it. I'm guessing it happens during select from 'things' that do not exist anymore. Not sure if these things are tables or something else. This is what I gathered from my research.

Any idea how to create my tables? I tried to execute multiple times the SQL code, but to no avail - I get the same error every time.

Also the table is not created:

SQL> select * from bns_saa_messages;
select * from bns_saa_messages
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

I've looked at the following so questions, but it doesn't seem to be helping. Maybe you can see something I don't:
I get an ORA-01775: looping chain of synonyms error when I use sqlldr
How to debug ORA-01775: looping chain of synonyms?
ORA-01775: looping chain of synonyms but there are no synonyms

Thanks

:UPDATE:

After executing: select * from all_synonyms where synonym_name = 'BNS_SAA_MESSAGES'; as per Craig's suggestion I get:

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------
PUBLIC                         BNS_SAA_MESSAGES
ORDMSO                         BNS_SAA_MESSAGES

:UPDATE: 2
Running: select * from all_tables where table_name = 'BNS_SAA_MESSAGES';

SQL> select * from all_tables where table_name = 'BNS_SAA_MESSAGES';

no rows selected

Answer 1

I would run this to see where the synonym is actually pointing:

select *
from all_synonyms
where synonym_name = 'BNS_SAA_MESSAGES'

I am guessing the synonym is pointing to the wrong TABLE_OWNER.

UPDATE

So where is the table actually at? You can find this using:

select *
from all_tables
where table_name = 'BNS_SAA_MESSAGES'

If table_owner is not 'ORDMSO', then you need to either update the synonym to point to the correct location or run the Create table... script as ORDMSO.

UPDATE2

Can you run the Create table... script as ORDMSO? If not, you are going to need to have someone with higher privileges run:

select *
from dba_tables
where table_name = 'BNS_SAA_MESSAGES'

to figure out where the table really is, and then update the synonym accordingly.

Answer 2

It looks like the output is from running this a second time, which you hinted at; the ORA-00955 from the create public synonym shows that has been done before somewhere (as does the all_synonyms query you ran), and it clearly exists from the other errors. You wouldn't have got exactly these errors the first time you ran it, but would on every subsequent run.

At a mimimum the first code snippet should drop the public synonym before dropping the table, if you want it to be rerunnable.

But the first snippet doesn't seem to be run at all. There are no success or failure messages from Oracle. The only real clue to why is this:

Processing bns_saa_messages
cat: cannot open bns_saa_messages.sql

Which is a shell script problem, not really an Oracle one. Without seeing the shell script it's rather hard to tell quite what's wrong, but I suspect the script is building a temporary .sql file from various sources and then running it through SQL*Plus; but the vital bns_saa_messages.sql file is missing. Presumably that's where the first snippet is supposed to be; and since that seems to exist, this could beas simple as a name mismatch between the file and what the script is expecting, or the script is doing a cd and the file is in the wrong directory, or something equally trivial. But maybe not... not enough info.

Answer 3

Are you generating an export?

My solution with a BD oracle 11g was:

See the SYS_EXPORT objects, use Oracle SQL Developer, connect to the DB and execute the following:

Select owner, object_name, object_type, status from dba_objects where object_name like '%SYS_EXPORT%'

Objects SYS_EXPORT

Then with the user: sqlplus / as sysdba removes the created objects.

Delete objects SYS_EXPORT

SQL>drop public synonym sys_export_full_01;

SQL>drop public synonym sys_export_full_02;

SQL>drop public synonym sys_export_full_03;

SQL>drop public synonym sys_export_full_04;

After this you can generate the export.