Re: Public synonym not really there

vlad wrote:

> 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>
Synonyms are not grants. Did you consider

GRANT SELECT ON <table_name> TO sys?

Daniel Morgan Received on Fri Feb 21 2003 - 15:12:08 CST