sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

案例:Oracle缺少同义词DDL语句无法执行 ORA-00604 ORA-00942

时间:2016-05-22 09:55   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

在执行DDL语句时无法执行,报错ORA 00604 ORA 00942,通过隐含参数来解决同义词问题

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

1.Oracle缺少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

2.尝试重建同义词

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('%¶m%')
  7  order by name
  8  /
Enter value for param: SYSTEM_TRIG_ENABLED
old   6:    and upper(a.ksppinm) LIKE upper('%¶m%')
new   6:    and upper(a.ksppinm) LIKE upper('%SYSTEM_TRIG_ENABLED%')

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

3.设置_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.

4.使用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研究中心
http://www.oracleplus.net
本文由大师惜分飞原创分享,转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle缺少同义词DDL语句无法执行 ORA-00604 ORA-00942

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/212.html

Oracle研究中心

关键词:

ORA-00604

ORA-00942

Oracle缺少同义词DDL语句无法执行

ORA-00604 ORA-00942