2

Using SQLDeveloper 4.0.1.14 to create an export script (separate files & "drops" checked), it generated me those 4 lines among others in DROP.sql:

DROP SYNONYM "PUBLIC"."DUAL";
DROP SYNONYM "PUBLIC"."DBMS_SQL";
DROP SYNONYM "PUBLIC"."DBMS_LOCK";
DROP SYNONYM "PUBLIC"."DBMS_OUTPUT";

Now that I have accidentally passed the whole script using SYSTEM user, I can no longer do modification (create or drop tables) to the database, I have that error popping:

An error was encountered performing the requested operation:

ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
       (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
       can be corrected, do so; otherwise contact Oracle Support.
Vendor code 604

The problem is that I'm getting that error event when I try this:

CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "SYS"."DUAL";

I precise that the SYS.DUAL table still exists as SELECT 1 FROM SYS.DUAL works but SELECT 1 FROM DUAL fails with ORA-00942: table or view does not exist.

I tried to recreate the synonym as SYSTEM and SYSDBA with the same failure.

Can I recreate those synonyms with another way?

Anthony O.
  • 22,041
  • 18
  • 107
  • 163

1 Answers1

2

Try it as SYS but without the doauble quotes, i.e.:

CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS.DUAL;

not 

CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "SYS"."DUAL";

As I understand it the double quotes make the object name case sensitive.

Update - If you have access to metalink then you will find the answer in note 973260.1, something aboput a trigger firing :

ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS.DUAL;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=true SCOPE=MEMORY;

The note suggest that if this doesnt work, query DBA_TRIGGERS to find a BEFORE CREATE trigger enabled, and if found disable it and then re-issue create synonym statement, then re-enable the trigger.

TenG
  • 3,843
  • 2
  • 25
  • 42
  • No sorry, this is not working either: `ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist 00604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). *Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.` – Anthony O. Jun 01 '15 at 08:05
  • Please run this as SYS and post the result here: SELECT object_name, object_type, owner, status from dba_objects where object name = 'DUAL'; – TenG Jun 01 '15 at 23:23
  • fixing `object name` with `object_name` in your request I obtain one result: `OBJECT_NAME OBJECT_TYPE OWNER STATUS ----------- ----------- ----- ------ DUAL TABLE SYS VALID` – Anthony O. Jun 02 '15 at 14:51
  • Try "Create or replace public synonym mydual for sys.dual". Just to see where the issue is ... I think table "dual" itself exists, so it must be some witchcraft within. Might also be worth looking at the trace file generated to see which table the 604 error is referring to. – TenG Jun 02 '15 at 16:44
  • THANK YOU ! It worked disabling the system triggers, recreating the deleted `DUAL`, `DBMS_SQL`, `DBMS_LOCK` and `DBMS_OUTPUT` public synonyms. – Anthony O. Jun 08 '15 at 15:34