first,
I found there are long job running:
SQL> select * from dba_jobs_running;
SID JOB FAILURES LAST_DATE
---------- ---------- ---------- --------------
LAST_SEC THIS_DATE
------------------------------------------------ --------------
THIS_SEC INSTANCE
------------------------------------------------ ----------
15 364 9 07-9月 -08
09:25:15 09-9月 -08
02:00:02 0
53 906 6 05-9月 -08
21:43:28 08-9月 -08
06:50:11 0
SID JOB FAILURES LAST_DATE
---------- ---------- ---------- --------------
LAST_SEC THIS_DATE
------------------------------------------------ --------------
THIS_SEC INSTANCE
------------------------------------------------ ----------
170 925 0 08-9月 -08
20:39:51 09-9月 -08
01:51:41 0
second,
1 SELECT /*+ RULE */
2 LS.OSUSER OS_USER_NAME,
3 LS.USERNAME USER_NAME,
4 DECODE(LS.TYPE,
5 'RW','ROW WAIT ENQUEUE LOCK',
6 'TM','DML ENQUEUE LOCK',
7 'TX','TRANSACTION ENQUEUE LOCK',
8 'UL','USER SUPPLIED LOCK') LOCK_TYPE,
9 O.OBJECT_NAME OBJECT,
10 DECODE(LS.LMODE,
11 1,NULL,
12 2,'ROW SHARE',
13 3,'ROW EXCLUSIVE',
14 4,'SHARE',
15 5,'SHARE ROW EXCLUSIVE',
16 6,'EXCLUSIVE',
17 NULL) LOCK_MODE,
18 O.OWNER,
19 LS.SID,
20 LS.SERIAL# SERIAL_NUM,
21 LS.ID1,
22 LS.ID2
23 FROM SYS.DBA_OBJECTS O,
24 (SELECT S.OSUSER,
25 S.USERNAME,
26 L.TYPE,
27 L.LMODE,
28 S.SID,
29 S.SERIAL#,
30 L.ID1,
31 L.ID2
32 FROM V$SESSION S, V$LOCK L
33 WHERE S.SID = L.SID) LS
34 WHERE O.OBJECT_ID = LS.ID1
35 AND O.OWNER <> 'SYS'
36* ORDER BY O.OWNER, O.OBJECT_NAME
OS_USER_NAME
------------------------------------------------------------
USER_NAME
------------------------------------------------------------
LOCK_TYPE
----------------------------------------------------------------
OBJECT
--------------------------------------------------------------------------------
LOCK_MODE
----------------------------------------------------------------
OWNER SID
------------------------------------------------------------ ----------
SERIAL_NUM ID1 ID2
---------- ---------- ----------
oracle
CCIM
DML ENQUEUE LOCK
SC_CLIENT_REGISTRATION
ROW EXCLUSIVE
CCIM 15
4449 87376 0
oracle
CCIM
SC_CLIENT_REGISTRATION
EXCLUSIVE
CCIM 15
4449 87376 0
oracle
SYS
/ea877347_JWindowAccessibleJWi
SHARE
PUBLIC 132
16294 25622 0
oracle
V$TRANSACTION
SHARE
PUBLIC 45
1 1023 0
oracle
REPORTER
DML ENQUEUE LOCK
REPORTER_STATUS_MV
ROW EXCLUSIVE
REPORTER 53
1402 44178 0
oracle
REPORTER
REPORTER_STATUS_MV
EXCLUSIVE
REPORTER 53
1402 44178 0
oracle
SGREPORTS
SUMMARY
EXCLUSIVE
SGREPORTS 170
20652 90595 0
oracle
SGREPORTS
DML ENQUEUE LOCK
SUMMARY
ROW EXCLUSIVE
SGREPORTS 170
20652 90595 0
oracle
SGREPORTS
UI_TYPETOSERVICE
EXCLUSIVE
SGREPORTS 170
20652 92411 0
third,
SQL> select * from DBA_2PC_PENDING;
LOCAL_TRAN_ID
--------------------------------------------
GLOBAL_TRAN_ID
--------------------------------------------------------------------------------
STATE MIXED AD
-------------------------------- ------ --
TRAN_COMMENT
--------------------------------------------------------------------------------
FAIL_TIME FORCE_TIME RETRY_TIME
-------------- -------------- --------------
OS_USER
--------------------------------------------------------------------------------
OS_TERMINAL
--------------------------------------------------------------------------------
HOST
--------------------------------------------------------------------------------
DB_USER
------------------------------------------------------------
COMMIT#
--------------------------------
10.29.971712
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.29.971712
collecting no
06-2月 -08 02-9月 -08
localuser
GROUCHY
MSHOME/GROUCHY
REPORTER
8312022983562
10.33.931719
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.33.931719
collecting no
06-1月 -08 02-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8308525688197
10.24.983207
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.24.983207
collecting no
23-2月 -08 05-9月 -08
oracle
UNKNOWN
nile002a-zone01
SGREPORTS
8314053160664
7.4.216944
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.7.4.216944
collecting no
07-4月 -08 06-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8319660930334
5.27.102183
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.5.27.102183
collecting no
09-4月 -08 03-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8319831100656
7.46.226911
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.7.46.226911
collecting no
06-6月 -08 04-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
REPORTER
8328880245773
The earliest one is 06-1月 -08.
I am shocked by this lock:
I found there are long job running:
SQL> select * from dba_jobs_running;
SID JOB FAILURES LAST_DATE
---------- ---------- ---------- --------------
LAST_SEC THIS_DATE
------------------------------------------------ --------------
THIS_SEC INSTANCE
------------------------------------------------ ----------
15 364 9 07-9月 -08
09:25:15 09-9月 -08
02:00:02 0
53 906 6 05-9月 -08
21:43:28 08-9月 -08
06:50:11 0
SID JOB FAILURES LAST_DATE
---------- ---------- ---------- --------------
LAST_SEC THIS_DATE
------------------------------------------------ --------------
THIS_SEC INSTANCE
------------------------------------------------ ----------
170 925 0 08-9月 -08
20:39:51 09-9月 -08
01:51:41 0
second,
1 SELECT /*+ RULE */
2 LS.OSUSER OS_USER_NAME,
3 LS.USERNAME USER_NAME,
4 DECODE(LS.TYPE,
5 'RW','ROW WAIT ENQUEUE LOCK',
6 'TM','DML ENQUEUE LOCK',
7 'TX','TRANSACTION ENQUEUE LOCK',
8 'UL','USER SUPPLIED LOCK') LOCK_TYPE,
9 O.OBJECT_NAME OBJECT,
10 DECODE(LS.LMODE,
11 1,NULL,
12 2,'ROW SHARE',
13 3,'ROW EXCLUSIVE',
14 4,'SHARE',
15 5,'SHARE ROW EXCLUSIVE',
16 6,'EXCLUSIVE',
17 NULL) LOCK_MODE,
18 O.OWNER,
19 LS.SID,
20 LS.SERIAL# SERIAL_NUM,
21 LS.ID1,
22 LS.ID2
23 FROM SYS.DBA_OBJECTS O,
24 (SELECT S.OSUSER,
25 S.USERNAME,
26 L.TYPE,
27 L.LMODE,
28 S.SID,
29 S.SERIAL#,
30 L.ID1,
31 L.ID2
32 FROM V$SESSION S, V$LOCK L
33 WHERE S.SID = L.SID) LS
34 WHERE O.OBJECT_ID = LS.ID1
35 AND O.OWNER <> 'SYS'
36* ORDER BY O.OWNER, O.OBJECT_NAME
OS_USER_NAME
------------------------------------------------------------
USER_NAME
------------------------------------------------------------
LOCK_TYPE
----------------------------------------------------------------
OBJECT
--------------------------------------------------------------------------------
LOCK_MODE
----------------------------------------------------------------
OWNER SID
------------------------------------------------------------ ----------
SERIAL_NUM ID1 ID2
---------- ---------- ----------
oracle
CCIM
DML ENQUEUE LOCK
SC_CLIENT_REGISTRATION
ROW EXCLUSIVE
CCIM 15
4449 87376 0
oracle
CCIM
SC_CLIENT_REGISTRATION
EXCLUSIVE
CCIM 15
4449 87376 0
oracle
SYS
/ea877347_JWindowAccessibleJWi
SHARE
PUBLIC 132
16294 25622 0
oracle
V$TRANSACTION
SHARE
PUBLIC 45
1 1023 0
oracle
REPORTER
DML ENQUEUE LOCK
REPORTER_STATUS_MV
ROW EXCLUSIVE
REPORTER 53
1402 44178 0
oracle
REPORTER
REPORTER_STATUS_MV
EXCLUSIVE
REPORTER 53
1402 44178 0
oracle
SGREPORTS
SUMMARY
EXCLUSIVE
SGREPORTS 170
20652 90595 0
oracle
SGREPORTS
DML ENQUEUE LOCK
SUMMARY
ROW EXCLUSIVE
SGREPORTS 170
20652 90595 0
oracle
SGREPORTS
UI_TYPETOSERVICE
EXCLUSIVE
SGREPORTS 170
20652 92411 0
third,
SQL> select * from DBA_2PC_PENDING;
LOCAL_TRAN_ID
--------------------------------------------
GLOBAL_TRAN_ID
--------------------------------------------------------------------------------
STATE MIXED AD
-------------------------------- ------ --
TRAN_COMMENT
--------------------------------------------------------------------------------
FAIL_TIME FORCE_TIME RETRY_TIME
-------------- -------------- --------------
OS_USER
--------------------------------------------------------------------------------
OS_TERMINAL
--------------------------------------------------------------------------------
HOST
--------------------------------------------------------------------------------
DB_USER
------------------------------------------------------------
COMMIT#
--------------------------------
10.29.971712
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.29.971712
collecting no
06-2月 -08 02-9月 -08
localuser
GROUCHY
MSHOME/GROUCHY
REPORTER
8312022983562
10.33.931719
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.33.931719
collecting no
06-1月 -08 02-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8308525688197
10.24.983207
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.24.983207
collecting no
23-2月 -08 05-9月 -08
oracle
UNKNOWN
nile002a-zone01
SGREPORTS
8314053160664
7.4.216944
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.7.4.216944
collecting no
07-4月 -08 06-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8319660930334
5.27.102183
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.5.27.102183
collecting no
09-4月 -08 03-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8319831100656
7.46.226911
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.7.46.226911
collecting no
06-6月 -08 04-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
REPORTER
8328880245773
The earliest one is 06-1月 -08.
I am shocked by this lock: