dual 缺少同义词故障解决

发表于 2012 年 10 月 30 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：dual 缺少同义词故障解决

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在最近的一个客户案例中,因为缺少dual同义词,导致ddl语句无法执行。这里_system_trig_enabled参数和upgrade模式两种来解决该问题,整体上来说_system_trig_enabled不用重启数据库终止业务,更加人性化.
缺少dual同义词后果

SQL> create table t_xifenfei_dual as
  2  select * from dba_objects;
select * from dba_objects
              *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

SQL> alter session set events '942 trace name errorstack level 3';

Session altered.

SQL> create table t_xifenfei_dual as  select * from dba_objects;
create table t_xifenfei_dual as  select * from dba_objects
                                               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

--trace文件
*** 2012-09-29 12:37:05.156
ksedmp: internal or fatal error
ORA-00942: table or view does not exist
Current SQL statement for this session:
select dummy from dual where  ora_dict_obj_type = 'SYNONYM' 
AND ora_dict_obj_owner = 'PUBLIC'

--dual 对象
SQL> select object_type,owner from dba_objects where object_name='DUAL';

OBJECT_TYPE         OWNER
------------------- ------------------------------
TABLE               SYS

尝试重建同义词

SQL> create public synonym dual for dual;
create public synonym dual for dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

_system_trig_enabled参数

SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: SYSTEM_TRIG_ENABLED
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%SYSTEM_TRIG_ENABLED%')

NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ -----------------------------
_system_trig_enabled             TRUE                     are system triggers enabled

设置_SYSTEM_TRIG_ENABLED重建dual同义词

SQL> ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;

System altered.

SQL> create public synonym dual for dual;

Synonym created.


SQL> ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=true SCOPE=MEMORY;

System altered.

使用upgrade模式创建

SQL> drop PUBLIC SYNONYM dual;

Synonym dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> create public synonym dual for dual;
create public synonym dual for dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrage;
SP2-0714: invalid combination of STARTUP options
SQL> startup upgrape;
SP2-0714: invalid combination of STARTUP options
SQL> startup upgrade
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> create public synonym dual for dual;

Synonym created.

SQL> startup force
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
此条目发表在 Oracle 分类目录，贴了 标签。将固定链接加入收藏夹。

《dual 缺少同义词故障解决》有 1 条评论

  1. 惜分飞 说：
    2012 年 10 月 30 日12:39

    ‘Dual’ Synonym was Dropped by Mistake and Cannot Recreate it [ID 973260.1]

    Applies to: 
Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms
 -- Problem Statement:
Dropped the 'dual' synonym by mistake.  As a result, when trying to access dual, an error is given:

SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-00942: table or view does not exist

The 'dual' table dual exist:


SQL> show user
USER is "SYS"

SQL> select table_name from dba_tables where table_name='DUAL';

TABLE_NAME
------------------------------
DUAL


However, cannot create the synonym for dual:


SQL> create public synonym dual for dual
2 ;
create public synonym dual for dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Also receiving the same errors if try to create any table for any user including SYS.
 
Changes
 Dropped 'dual' synonym by mistake. 

Cause
 Review the trace file after performing following:
SQL> alter session set events '942 trace name errorstack level 3';
SQL> create public synonym dual for dual;

The failing SQL statement is:

 select dummy from dual where ora_dict_obj_type = 'SYNONYM' AND 
ora_dict_obj_owner = 'PUBLIC'

It appears a trigger is being fired prior to the create statement.
 
Solution
======================
-- To implement the solution, please execute the following steps::
It appears a before create trigger is firing before issuing the create synonym statement.

1- Issue:
SQL>ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
SQL> create or replace public synonym dual for sys.dual;
SQL>ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=true SCOPE=MEMORY;

2- If that still fails, query dba_triggers to determine if you have a before create trigger enabled.
If yes, disable it and then re-issue create synonym statement.