Hi!
We have a very annoying situation. One developer who was logged into database as user A run following SQL statements (don't ask me why):
CREATE OR REPLACE PUBLIC SYNONYM "USER_TABLES" FOR "USER_TABLES";
CREATE OR REPLACE PUBLIC SYNONYM "DBMS_STATS" FOR "DBMS_STATS";
CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "DUAL";
As a result lot of thing stopped working in database. For example
select 1 from dual;
gives ORA-01775: looping chain of synonyms
If I log on as SYS and try to recreate correct synonum:
SQL> create public synonym dual for sys.dual;
create public synonym dual for sys.dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
Does anybody have any ideas how to fix this issue without point in time recovery?