在最近的一个客户案例中,因为缺少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.