Problems caused by table sys. dual being cleared

Problems caused by table sys. dual being cleared

A few days ago, I received a report from the application staff saying that the application could not connect to the database and reported the ORA error. This is an Oracle 9.2.0.8 RAC database (it has a long history ), the following error is reported when you manually connect to 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,200 2, 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:

We can see that a trigger is called during login, and the error is thrown by the trigger.

Use sqlplus '/as sysdba' to CONNECT to the database and check the trigger BOMS30.ON _ CONNECT.
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 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 ''';

The content is very simple, so I manually debug it one by one and found that the following statements for getting environment variables are not output at all.
SYS @ tstdb1-SQL> SELECT sys_context ('userenv', 'session _ user') FROM dual;

No rows selected

Because it is a dual-node RAC, and then try to query the gv $ session, gv $ instance and other views are reported ORA-00600 Error
Select * from gv $ instance;

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

The Job process in alert. log also reports a similar error:
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

* ** The content of the newshwg2_j000_503990.trc file is as follows:
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
----- PL/SQL Call Stack -----
Object line object
Handle number name
70000046fdf4208 3 procedure WANGGUAN. RECORD_SESSION
7000004dcee7188 1 anonymous block
... Some content is omitted.

However, it is OK to query local views not starting with gv $.

The first response was that the dual table record was deleted.
Select * from dual;

No rows selected

The solution is simple. Just insert a record.
Insert into dual values ('x ');
Commit;

SYS @ tstdb1-SQL> select * from dual;

D
-
X

Previously a number of strange problems have been restored: the user can log on to the database, sqlplus login is no longer displayed "SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level. ", the gv $ view can also be queried normally;

After the fault is solved, we will briefly explore the DUAL table:

DUAL is a table under SYS, and a public synonym is built on it. We usually use non-SYS users to access 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

--- Insert a record to the DUAL table, indicating that the insert is successful, but only one record is displayed in the dual table.
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

* ** Although only one record is displayed, two records are actually stored on the disk and can be verified through the 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.

The following Trace file is displayed:
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 <---- original record 'X'
Tab 0, row 1, @ 0x1f96
Tl: 5 fb: -- H-FL -- lb: 0x2 cc: 1
Col 0: [1] 41 <---- records newly inserted but not displayed 'A'
End_of_block_dump
End dump data blocks tsn: 0 file #: 1 minblk 929 maxblk 929

It is determined that oracle should filter out Redundant records during the execution of select * from dual, and always only one record is returned, a bit similar to adding rowum <2 in the where condition, the following series of queries verify this
SYS @ tstdb1-SQL> select * from dual;

D
-
X

SYS @ tstdb1-SQL> select * from dual where dummy = 'a'; <--- after the "= A" condition is added, 'A' can be returned'

D
-
A

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

D
-
X

SYS @ tstdb1-SQL> select * from dual where dummy! = 'X'; <--- added "! = 'X' can also be returned after the condition is 'A'

D
-
A

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

D
-
X

At this time, we will insert a record into the dual table.
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'; <--- always returns the record with the smallest row number.

D
-
A

Update dual set dummy = 'A' where dummy = 'a ';
Commit;

SYS @ tstdb1-SQL> select * from dual where dummy! = 'X'; <--- always returns records with a smaller row number.

D
-
A

Three records are stored in the 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

Delete the "dummy = 'A'" Record
Delete dual where dummy = 'a ';
Commit;

The block dump contains the following content:
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' marked as deleted
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'; <--- because the row 1 record has been deleted, row 2: B is returned.

D
-
B

Deletion without conditions is also a small Delete with rownum selected
Delete dual;
Commit;

SYS @ tstdb1-SQL> select * from dual;

D
-
B

When there is only one record in the dual table, do not delete it again (although oracle will not prevent you from deleting the last record). This will cause various problems described at the beginning of this article.
SYS @ tstdb1-SQL> select * from dual where dummy! = 'B ';

No rows selected

Summary:
A Dual table is a table maintained by Oracle itself. Although it is a common table, oracle has some special processing for it:
After performing insert and update operations on the dual table, executing select * from dual without any conditions will only return one of the records, which is the smallest record of rownum;
If you run delete dual for a table without adding a condition, a record with a smaller rownum will be deleted. If there is only one record in the table, the table will be cleared;
The dual table is used to return function values such as sysdate and recursively call some internal views. Do not modify it easily; otherwise, unexpected "effects" may occur"