【故障-ORACLE】_NEXT_OBJE Exclusive鎖問題分析

 

SID	SERIAL#	USERNAME	TERMINAL	RES	TAB	OWNER	ID1	ID2	LMODE	REQUEST	CTIME
158	1		UNKNOWN	XRSystem	TAB$	SYS	4	0	No Lock		2500766
158	1		UNKNOWN	RSSystem	PROXY_ROLE	SYS	25	1	Row Share		2500746
159	1		UNKNOWN	RTSystem	_NEXT_OBJE	SYS	1	0	Exclusive		2500746
160	1		UNKNOWN	DMSystem	_NEXT_OBJE	SYS	1	0	Share		2500744
159	1		UNKNOWN	RTSystem	_NEXT_OBJE	SYS	1	1	Exclusive		2500746
160	1		UNKNOWN	RTSystem	_NEXT_OBJE	SYS	1	2	Exclusive		2500746
160	1		UNKNOWN	MRSystem	_NEXT_OBJE	SYS	1	0	Share		2500746
160	1		UNKNOWN	MRSystem	C_OBJ#	SYS	2	0	Share		2500746
160	1		UNKNOWN	MRSystem	I_OBJ#	SYS	3	0	Share		2500746
160	1		UNKNOWN	MRSystem	TAB$	SYS	4	0	Share		2500746
160	1		UNKNOWN	MRSystem	CLU$	SYS	5	0	Share		2500746
160	1		UNKNOWN	MRSystem	C_TS#	SYS	6	0	Share		2500746
160	1		UNKNOWN	MRSystem	I_TS#	SYS	7	0	Share		2500746
160	1		UNKNOWN	MRSystem	C_FILE#_BL	SYS	8	0	Share		2500746
160	1		UNKNOWN	MRSystem	I_FILE#_BL	SYS	9	0	Share		2500746
160	1		UNKNOWN	MRSystem	I_NTAB3	SYS	201	0	Share		2500746
157	1		UNKNOWN	TSSystem	I_OBJ#	SYS	3	1	Row Exclusive		2500743
100	26292	CASKWMES	pts/31	TM - DML Enqueue	RATION_REM	CASKWMES	106686	0	Row Exclusive		19
100	26292	CASKWMES	pts/31	TM - DML Enqueue	RATION_DAT	CASKWMES	106658	0	Row Exclusive		10

在OTN尋找到些解釋：

LGWR is always holds RT lock. RT is a redo thread and it always be presented since database is up. This is a mark of healthy database.

DBWR also holds MR locks upon every datafile of database. It is also a mark of healthy database.

--from the database without big user activity
oratest> select * from v$lock;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

--------

--

----------

----------

----------
6831B244 6831B254 2 MR 201 0 4 0 27567 0
6831B1F8 6831B208 2 MR 12 0 4 0 27567 0
6831B1AC 6831B1BC 2 MR 11 0 4 0 27567 0
6831B160 6831B170 2 MR 10 0 4 0 27567 0
6831B114 6831B124 2 MR 9 0 4 0 27567 0
6831B0C8 6831B0D8 2 MR 8 0 4 0 27567 0
6831B07C 6831B08C 2 MR 7 0 4 0 27567 0
6831B030 6831B040 2 MR 6 0 4 0 27567 0
6831AFE4 6831AFF4 2 MR 5 0 4 0 27567 0
6831AF98 6831AFA8 2 MR 4 0 4 0 27567 0
6831AF4C 6831AF5C 2 MR 3 0 4 0 27567 0
6831AF00 6831AF10 2 MR 2 0 4 0 27567 0
6831AEB4 6831AEC4 2 MR 1 0 4 0 27567 0
6831ADD0 6831ADE0 3 RT 1 0 6 0 27576 0
6831ACA0 6831ACB0 4 XR 4 0 1 0 27582 0
6831AE68 6831AE78 5 TS 2 1 3 0 27555 0

_NEXT_OBJECT is the hidden objects which generates the next object_id or data_object_id every time when new object is created or truncate is issued on existing tables. Waits on that object is the criteria of repeated object creations and/or truncates.

oratest> select object_id from user_objects where object_name = 'A1';

OBJECT_ID

26302

oratest> truncate table a1;

Table truncated.

oratest> create table a11(c1 int);

Table created.

oratest> select object_id from user_objects where object_name = 'A11';

OBJECT_ID

26304

oratest> create table a111(c1 int);

Table created.

oratest> select object_id from user_objects where object_name = 'A111';

OBJECT_ID

26305