如何重新创建公共同义词"DUAL"? [英] How to recreate public synonym "DUAL"?

问题描述

使用SQLDeveloper 4.0.1.14创建一个导出脚本(检查了单独的文件和"drops")，它在DROP.sql中为我生成了这4行:

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";

现在，我不小心使用了SYSTEM用户传递了整个脚本，我无法再对数据库进行修改(创建或删除表)，弹出该错误:

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";

我精确地认为SYS.DUAL表在SELECT 1 FROM SYS.DUAL工作时仍然存在，但SELECT 1 FROM DUAL失败并显示ORA-00942: table or view does not exist.

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.

我尝试用相同的错误将同义词重新创建为SYSTEM和SYSDBA.

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

我可以用其他方式重新创建这些同义词吗?

Can I recreate those synonyms with another way?

推荐答案

将其作为SYS尝试，但不要使用双引号，即:

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

CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS.DUAL;

不是

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

据我了解，双引号使对象名称区分大小写.

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

更新-如果您有权使用metalink，那么您将在注释973260.1中找到答案，这似乎触发了触发:

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;

该注释建议，如果此方法不起作用，请查询DBA_TRIGGERS以查找启用的BEFORE CREATE触发器，如果​​发现禁用了该触发器，然后重新发出创建同义词语句，然后重新启用该触发器.

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.

这篇关于如何重新创建公共同义词"DUAL"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！