ITPub博客

首页 > 数据库 > Oracle > sys.dual表被清空后引发的问题

sys.dual表被清空后引发的问题

原创 Oracle 作者:oliseh 时间:2015-11-20 15:53:16 0 删除 编辑

前几天收到过应用人员的一个报障称,应用程序无法连接数据库,报了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等函数值的返回和一些内部视图访问时的递归调用,千万不要轻易的修改它,否则会产生意想不到的“效果


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1842101/,如需转载,请注明出处,否则将追究法律责任。

上一篇: kill session的学问
请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1642270