ORA-00030: User session ID does not exist., ora-00030exist
When my colleague executed the SQL statement in Toad, the wireless network was suddenly interrupted. Let me check the details as follows (some information should be replaced by xxx, because it is a special user who is processing historical archive data, you can use the following SQL statement to find the corresponding session information ):
SQL> SELECT B.USERNAME ,
2 B.SID ,
3 B.SERIAL# ,
4 LOGON_TIME ,
5 A.OBJECT_ID
6 FROM V$LOCKED_OBJECT A, V$SESSION B
7 WHERE A.SESSION_ID = B.SID AND B.USERNAME=&USERNAME
8 ORDER BY B.LOGON_TIME;
USERNAME SID SERIAL# LOGON_TIM OBJECT_ID
------------------------------ ---------- ---------- --------- ----------
xxxxxx 523 41890 06-MAY-16 825891
xxxxxx 523 41890 06-MAY-16 825892
After the statement of the kill session is executed, check that the corresponding session still exists, but the SERIAL # value has changed, when the kill session is again, a ORA-00030 error occurs, as shown below
SQL> alter system kill session '523, 41890' immediate;
System altered.
SQL> SELECT A.ORACLE_USERNAME ,
2 A.OS_USER_NAME ,
3 B.OWNER ,
4 B.OBJECT_NAME ,
5 A.SESSION_ID ,
6 A.PROCESS ,
7 A.LOCKED_MODE
8 FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
9 WHERE B.OBJECT_ID = A.OBJECT_ID AND B.OWNER=&OWNER
10 ORDER BY A.ORACLE_USERNAME,
11 A.OS_USER_NAME;
ORACLE_USERNAME OS_USER_NAME OWNER OBJECT_NAME SESSION_ID PROCESS LOCKED_MODE
---------------- ------------- ----------- ----------------- ---------------------- -------------
xxxxxxxxxxxxxxx ZhanxxxnL xxxxxxxxxxxx INV_xxxx_HD 523 6208:7548 3
xxxxxxxxxxxxxxx ZhanxxxxL xxxxxxxxxxxx INV_xxxx_LINES 523 6208:7548 3
SQL> SELECT B.USERNAME ,
2 B.SID ,
3 B.SERIAL# ,
4 LOGON_TIME ,
5 A.OBJECT_ID
6 FROM V$LOCKED_OBJECT A, V$SESSION B
7 WHERE A.SESSION_ID = B.SID
AND B.USERNAME=&USERNAME
8 ORDER BY B.LOGON_TIME;
USERNAME SID SERIAL# LOGON_TIM OBJECT_ID
------------------------------ ---------- ---------- --------- ----------
xxxxxxxxxxxxxx 523 41891 06-MAY-16 825892
xxxxxxxxxxxxxx 523 41891 06-MAY-16 825891
SQL> alter system kill session '523, 41891' immediate;
alter system kill session '523, 41891' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
On metalink, you have viewed the description, cause, and solution of a ORA-00030 error. As shown below
SQL> ho oerr ora 30
00030,000 00, "User session ID does not exist ."
// * Cause: The user session ID no longer exists, probably because
// Session was logged out.
// * Action: Use a valid session ID.
The command may have been issued for one or more of the following reasons:
1. The process no longer exists at the OS level, but does show up as active in v $ session.
2. The user reboots the client machine without logging off, leaving a shadow process.
3. That session is holding onto a lock that needs to be released.
CAUSE
This error occurs because PMON is already trying to kill the session.
This is indicated by the fact that the serial number keeps changing.
When PMON attempts to cleanup a dead session, it will increase the serial number.
PMON may take a long time to clean up the process. If the process was doing a very large transaction at the time it aborted, then PMON has to rollback the large transaction.
When PMON makes progress, I. e. if it manages to free at least some of the process's resource, it will repeatedly keep trying to delete the process. when it finally gets to the point where it can't free up any of the process's resource (I. e. there are no more free buffers), it will print a message to the trace file and try to delete that process a second time.
The problem is encountered when PMON lacks the resources needed to remove the process. if there are not enough buffers, then the removal of the process is delayed. this is a free buffer problem in the data cache.
SOLUTION
Encountering a ORA-30 when attempting to manually kill a process is not necessarily a bug but a result of trying to kill a process already marked as killed.
PMON can take anywhere from 5 minutes to over 24 hours to clean up a job. The impact is that often the process being cleaned up is holding locks that prevents others from faster Ming certain operations.
The solution is to wait for PMON to clean up the process.
Basically, we can only wait for the pmon process to recycle and process the process. After ten minutes, the session process has not been cleared. So I checked the session information, you can view relevant information on the Internet and kill sessions at the system level.
SQL>
SQL> select event from v$session_wait where sid=523;
EVENT
----------------------------------------------------------------
db file sequential read
SQL> select sql_text from v$session a,v$sqltext_with_newlines b
2 where decode(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
3 and a.sid=&sid order by piece;
Enter value for sid: 523
old 3: and a.sid=&sid order by piece
new 3: and a.sid=523 order by piece
SQL_TEXT
----------------------------------------------------------------
DELETE from inv_xxx_lines WHERE (xxx) IN ( SELECT tr
ans_line_id FROM xxxx GROUP BY trans_line_id HAVING C
OUNT(xxxxx) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FRO
M xxxx GROUP BY xxx HAVING COUNT(*) > 1)
So I tried to kill the corresponding system process at the system level. After the execution is complete, the verification finds that the corresponding session has been killed. I don't know if the pmon process recycles this session process or can kill it from the system process (because it cannot re-launch this scenario). If we encounter this scenario next time, you can test and verify it. Record
SQL> ! kill -9 4884
References:
Https://support.oracle.com/epmos/faces/DocumentDisplay? _ AfrLoop = 533785808734847 & id = 1011386.6 & _ afrWindowMode = 0 & _ adf. ctrl-state = 13ipo04jjr_4
Http://www.linuxidc.com/Linux/2011-09/43730.htm