sys.dual表被清空後引發的問題

sys.dual表被清空後引發的問題

前幾天收到過應用人員的一個報障稱，應用程式無法串連資料庫，報了ORA的錯誤，這是一個Oracle 9.2.0.8的RAC資料庫(曆史夠悠久了吧），人工使用sqlplus串連報錯如下：
tstdb1@jq570322b:/home/tstdb1>sqlplus wangguan/773946@tstdb1

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Nov 15 21:27:27 2015

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-04088: error during execution of trigger 'BOMS30.ON_CONNECT'
ORA-01403: no data found
ORA-06512: at line 4

Enter user-name:

可以看出登陸時調用了一個trigger，錯誤是由trigger拋出的

使用sqlplus '/as sysdba'連上資料庫查看一下BOMS30.ON_CONNECT這個trigger
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.            <----雖然登陸成功了，但正常登陸的情況下應該是沒有這句話的，當時覺得資料庫裡肯定有異常

SYS@tstdb1-SQL> select source from dba_source where name='ON_CONNECT';

---trigger內容如下：
create or replace TRIGGER BOMS30.ON_CONNECT AFTER LOGON ON DATABASE
DECLARE
guser varchar2(30);
begin
SELECT sys_context('USERENV','SESSION_USER') into guser FROM dual;
if guser='BOMS30' THEN
EXECUTE IMMEDIATE 'alter session set nls_timestamp_format = ''YYYY-MM-DD HH24:MI:SS.FF''';

內容很簡單，於是手工逐句調試，發現下面擷取環境變數的語句盡然沒有輸出
SYS@tstdb1-SQL> SELECT sys_context('USERENV','SESSION_USER') FROM dual;

no rows selected

因為是雙節點的RAC，之後又嘗試查詢gv$session、gv$instance等視圖都報了ORA-00600錯誤
select * from gv$instance;

ORA-00600: internal error code, arguments: [15555], [], [], [], [], [], [], []

alert.log裡Job進程也報了類似的錯誤：
Errors in file /oracle/app/oracle/admin/newshwg/bdump/newshwg2_j000_503990.trc:
ORA-00600: internal error code, arguments: [15555], [], [], [], [], [], [], []
ORA-06512: at "WANGGUAN.RECORD_SESSION", line 3
ORA-06512: at line 1

*** newshwg2_j000_503990.trc檔案記錄的主要內容如下：
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [15555], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO WANGGUAN.SESSION_RECORD SELECT SYSDATE FROM_TIME, A.* FROM GV$SESSION A
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000046fdf4208        3  procedure WANGGUAN.RECORD_SESSION
7000004dcee7188        1  anonymous block
。。。省略了部分內容

但是查詢非gv$開頭的本地視圖都是OK的

看到的第一反應是dual表記錄被刪了，果不其然
select * from dual;

no rows selected

解決方案很簡單，插入一條記錄即可
insert into dual values('X');
commit;

SYS@tstdb1-SQL> select * from dual;

D
-
X

之前的若干詭異問題均恢複了：使用者能正常登陸資料庫，sqlplus登陸時不再顯示"SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level."，gv$視圖也能正常查詢了；

故障解決了，接著簡單探究一下DUAL表：

DUAL是SYS下的一張表，其上還建有一個public synonym，我們平時用非SYS使用者訪問的都是同義字DUAL
col object_name format a10
set linesize 80
select owner,object_name from dba_objects where object_name='DUAL';
SYS@tstdb1-SQL> select owner,object_type,object_name from dba_objects where object_name='DUAL';

OWNER                          OBJECT_TYPE        OBJECT_NAM
------------------------------ ------------------- ----------
SYS                            TABLE              DUAL
PUBLIC                        SYNONYM            DUAL

---往DUAL表insert一條記錄，顯示插入成功，但dual表裡依然只顯示一條記錄
SYS@tstdb1-SQL> select * from dual;

D
-
X

SYS@tstdb1-SQL> insert into dual values('A');

1 row created.

SYS@tstdb1-SQL> select * from dual;

D
-
X

SYS@tstdb1-SQL> commit;

Commit complete.

SYS@tstdb1-SQL> select * from dual;

D
-
X

***雖然只顯示一條記錄，但實際在磁碟上存放了兩條記錄，通過dump block可以驗證
SYS@tstdb1-SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                929                                    1

SYS@tstdb1-SQL> alter system dump datafile 1 block 929;

System altered.

截取的Trace檔案顯示：
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x11085f05c
    76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f96
avsp=0x1f78
tosp=0x1f78
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]    offs=0x1f9b
0x14:pri[1]    offs=0x1f96
block_row_dump:
tab 0, row 0, @0x1f9b
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  58                            <----原記錄'X'
tab 0, row 1, @0x1f96
tl: 5 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 1]  41                            <----新插入但未顯示的記錄'A'
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 929 maxblk 929

判斷oracle應該在執行select * from dual的過程中過濾掉了多餘的記錄，始終保持只返回一條記錄，有點類似在where條件裡隱含添加了rowum<2，下面的一系列查詢驗證了這一點
SYS@tstdb1-SQL> select * from dual;

D
-
X

SYS@tstdb1-SQL> select * from dual where dummy='A';    <---加了"=A"條件後可以返回'A'

D
-
A

SYS@tstdb1-SQL> select * from dual where dummy in ('A','X');

D
-
X

SYS@tstdb1-SQL> select * from dual where dummy!='X';    <---加了"!='X'"條件後也可以返回'A'

D
-
A

SYS@tstdb1-SQL> select * from dual where dummy!='A';

D
-
X

此時我們再往dual表裡插入一條記錄
insert into dual values('B');
commit;

SYS@tstdb1-SQL> select * from dual;

D
-
X

SYS@tstdb1-SQL> select * from dual where dummy='A';

D
-
A

SYS@tstdb1-SQL> select * from dual where dummy='B';

D
-
B

SYS@tstdb1-SQL> select * from dual where dummy!='X';    <---始終返回現存的row number最小的記錄

D
-
A

update dual set dummy='a' where dummy='A';
commit;

SYS@tstdb1-SQL> select * from dual where dummy!='X';  <---始終返回現存的row number小的記錄

D
-
a

此時的block裡存放了三條記錄
block_row_dump:
tab 0, row 0, @0x1f9b
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 1]  58                            <---row 0：'X'
tab 0, row 1, @0x1f96
tl: 5 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 1]  61                            <---row 1：'a'
tab 0, row 2, @0x1f91
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  42                            <---row 2：'B'
end_of_block_dump

刪除"dummy='a'"這條記錄
delete dual where dummy='a';
commit;

此時的block dump包含內容如下：
block_row_dump:
tab 0, row 0, @0x1f9b
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1        <---row 0 : 'X'
col  0: [ 1]  58
tab 0, row 1, @0x1f96
tl: 2 fb: --HDFL-- lb: 0x1                <---row 1 : 'a' 已標記為被刪除
tab 0, row 2, @0x1f91
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 1]  42                                <---row 2 : 'B'
end_of_block_dump

SYS@tstdb1-SQL> select * from dual;

D
-
X

SYS@tstdb1-SQL> select * from dual where dummy!='X';          <---因為row 1的記錄已經刪除，所以返回了row 2：B

D
-
B

不加條件的刪除，也是挑rownum較小的刪
delete dual;
commit;

SYS@tstdb1-SQL> select * from dual;

D
-
B

在dual表只剩一條記錄的情況下，千萬不要再刪了（雖然oracle不會阻止你刪除最後一條記錄），這樣會引發本文開頭所描述的各種問題
SYS@tstdb1-SQL> select * from dual where dummy!='B';

no rows selected

總結一下：
Dual表是Oracle自己維護的一張表，雖然是張普通表但是oracle對它有著一些特殊的處理：
對dual表進行insert、update操作後，不加條件的執行select * from dual只會返回其中的一條記錄，且是其中rownum最小的記錄；
對dual表不加條件的執行delete dual後會刪除其中rownum較小的一條記錄，如果表裡只有一條記錄，那麼表就被清空了；
dual表用於sysdate等函數值的返回和一些內部視圖訪問時的遞迴調用，千萬不要輕易的修改它，否則會產生意想不到的“效果”