联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在最近的一个客户案例中,因为缺少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('%¶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
设置_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.
‘Dual’ Synonym was Dropped by Mistake and Cannot Recreate it [ID 973260.1]