你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2016-03-17] 来源:Linux社区  作者:oliseh [字体: ]

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

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2016-03/129273.htm

linux
相关资讯       清空sys.dual表  sys.dual表 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款