Received the report of the business, saying that the report database has been slower recently, so I will check it up. As a result, unexpected discovery has an exceptional activity:
START_TIME SID Serial # segment_id segment_name process spid ses_addr lockwait buy_ublk -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ------------------------------------------------------------------------------------ -------------------- ---------- 12/30/05 01:48:33 71 20835 19 _syssmu19 $ 23055 15664 C0000001A45BC4A0 1
Start_time Sid Serial # ses_addr Xidusn Owner -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------------------------------------- Object_name --- -------------------------------------------------- -------------------------------------------------- ------------------------- OS_USER_NAME ORACLE_USERNAME ----------------------- ------------------------------------ 12/30/05 01:48:33 71 20839 C0000001A45BC4A0 19 reportwap_authprice_user_sp_rdreport16 Report
SID Serial # spid username Terminal Program -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------------------------- - 71 20913 15664 Oracle Unknown Oracle @ RDB01 (TNS V1-V3)
After repeated examination, we found that this returning segment is always Active, and it does not have any return to the rolling segment:
SQL> /
Sid Xidusn Used_UBLK ------------------------ 71 19 1
SQL> /
Sid Xidusn Used_UBLK ------------------------ 71 19 1
SQL> /
Sid Xidusn Used_UBLK ------------------------ 71 19 1
SQL>
What is more strange is that this SPID 15664 does not exist at the operating system! At this time, I have already begun, someone will have a busy only when this business is busy, so this SPID has not existed on the operating system, but because there is activity transaction exists in the rollback segment, Therefore, the SID of this transaction always exists in the database: Oracle @ rdb01: / tmp / lunar / rda / Output> PS-EF | GREP 15664 Oracle 21452 21450 0 12:48:10 PTS / TG 0:00 Grep 15664 Ocle @ rdb01 : / TMP / LUNAR / RDA / OUTPUT>
At this time, check waiting for events:
SID Event P1 P2 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ---------------------- 1 PMON timer 1000 0 71 control file sequential read 0 9 70 db file sequential read 410 67836 126 db file sequential read 284 82227 153 db file sequential read 429 211496 96 direct path read 98 47845 170 direct path read 163 5477 161 direct path read 1006 41733 61 Direct Path Write 1006 275069 5 SMON TIMER 300 0 97 PX Deq Credit: Need Buffer 268566527 2 86 PX Deq Credit: Send BLKD 268566527 1 26 PX Deq: Execute Reply 200 2 There is no harvest.
Now let's see which objects he are doing:
ENTER VALUE for SID: 71OLD 3: (Select SQL_HASH_VALUE FROM V $ session where sid = '& sid') New 3: (SELECT SQL_HASH_VALUE FROM V $ session where sid = '71 ')
SQL_Text ------------------------------------------------- . Icpcode,: counts,: period, to_date (: stat_time, 'yyyy-mm-dd hh24: mi: ss'))) then thinking, first Kill, try, try:
SQL> ALTER SYSTEM KILL session '71, 20979; alter system kill session '71, 20979 '* error at line 1: ORA-00030: User session ID DOES NOT EXIST.
SQL> SELECT SID, Serial # from v $ session where sid = 71;
SID SERIAL # ------ ------- 71 20986
SQL> /
SID SERIAL # ------ ------- 71 20986
SQL> /
SID SERIAL # ------ ------- 71 20986
SQL> ALTER SYSTEM KILL Session '71, 20986 '; ALTER SYSTEM KILL session '71, 20986' * error at line 1: ORA-00030: User session ID DOES NOT SIST.
SQL> SELECT SID, Serial # from v $ session where sid = 71;
SID Serial # ------ ------- 71 20988
SQL> ALTER SYSTEM KILL Session '71, 20988 '; ALTER SYSTEM KILL session '71, 20988' * error at line 1: ORA-00030: User session ID DOES NOT SIST.
SQL> It can be seen that this SID is not a normal session at all and cannot be killed.
