Dummy table dual problem.

This is what happened to my dual synonym in my testing database.I had dropped dual synonym after that I created a synonym using x$dual.

The real problem started now. I cant drop a table or synonym.

Non admin user login had received  following error.

SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level. 

ORA-00980: synonym translation is no longer valid


Solution :

Create public synonym for sys.dual .This only solve this problem.But for that I need to drop the already created synonym.





But this is what happened.

SQL> drop public synonym dual  force;
drop public synonym dual force
*

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00980: synonym translation is no longer valid

So I created dual synonym in all users including default schemas.

    connect sys/mypass as sysdba;
    create view x_$dual as select * from x$dual ;
    grant select on x_$dual to public;
    connect my_schema_owner/mypassword;
    create synonym DUAL for sys.x_$dual ;

After that I could able to drop tables and synonyms.

So don't drop dual table table or public synonym of dual table.