Public synonym not really there

From: vlad <bulk_at_sfatcu.com>
Date: Thu, 20 Feb 2003 02:32:11 GMT
Message-ID: <L6X4a.183304$be.164579@rwcrnsc53>

I would be very intered to know if anybody can explain the behavior below. I have Oracle 9.2 installed on my single-user home machine. A while ago I had created a public synonym called "countries" on "hr"."countries". Now, I logged in as SYS and tried to do SELECT * FROM countries. Well, you can see below what happened.

SQL> SELECT * FROM all_synonyms WHERE synonym_name LIKE '%COUNT%';

OWNER                          SYNONYM_NAME                   TABLE_OWNER
TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ --------------
---------------- ------------------------------ ----------------------------
----------------------------------------------------------------------------
------------------------
PUBLIC                         DEFERRCOUNT                    SYS
DEFERRCOUNT
OE                             COUNTRIES                      HR
COUNTRIES
PUBLIC                         COUNTRIES                      HR

COUNTRIES SQL> SELECT * FROM countries;

SQL> SELECT * FROM COUNTRIES;
SELECT * FROM COUNTRIES
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop public synonym countries;
drop public synonym countries

                    *

ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym countries for hr.countries; create public synonym countries for hr.countries *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-00001: unique constraint (SYS.I_OBJ2) violated

SQL> create public synonym countries for hr.countries; create public synonym countries for hr.countries

                      *

ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> drop public synonym countries;

Synonym dropped.

SQL> create public synonym countries for hr.countries;

Synonym created.

SQL> SELECT * FROM countries;

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina1                                        2
AU Australia                                         3
BE Belgia                                            1
BR Brazil                                            2
BU Bulgaria                                          1
CA Canada                                            2
CH Switzerland                                       1
CN China                                             3
DE Germany                                           1
DK Denmark                                           1
EG Egypt                                             4

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
FR France                                            1
HK HongKong                                          3
IL Israel                                            4
IN India                                             3
IT Italy                                             1
JP Japan                                             3
KW Kuwait                                            4
MX Mexico                                            2
NG Nigeria                                           4
NL Netherlands                                       1
SG Singapore                                         3

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
UK United Kingdom                                    1
US United States of America                          2
ZM Zambia                                            4
ZW Zimbabwe                                          4

26 rows selected.

SQL> Received on Wed Feb 19 2003 - 20:32:11 CST