So there I was, running emremove.sql as part of pre-upgrade task; however, it was taking longer than expected.
Session was stuck at the output shown below and desperately CTRL-C did not work.
14:35:05 472 /
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
^C
^C^C
^CI checked for blocking session and there were blocking locks from SYS which was really strange.
I made a gutsy call and kill SYS session from OS prompt based on timestamp.
~ $ ps -ef|grep sysdba
oracle 57147 231962 0 May12 pts/4 00:00:00 sqlplus as sysdba
oracle 155919 139352 0 14:34 pts/1 00:00:00 sqlplus as sysdba
oracle 244619 216760 0 15:25 pts/5 00:00:00 grep --color=auto sysdba
~ $ kill -9 155919
As it turns out, another DBA was logged in as sysdba causing havoc.
I was lucky to have killed the correct SYS session and will you be as lucky as I was?
Based on my near disaster, it would be better to create good practice of gathering your session info to be able to kill the correct session.
Here is current session info.
SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
2 where a.addr = b.paddr
3 and b.audsid = userenv('sessionid')
4 and b.sid=userenv('sid')
5 ;
SID SERIAL# PROCESSID CLIENTPID
---------- ---------- ------------------------ ------------------------
5 101 16428 16427
SQL>
[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16427
oracle 16427 8573 0 03:44 pts/0 00:00:00 sqlplus as sysdba
oracle 16428 16427 0 03:44 ? 00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 16461 11677 0 03:45 pts/1 00:00:00 grep --color=auto 16427
[oracle@ol7-112-dg1 ~]$
Kill OS process using sqlplus PROCESSID – don’t know session is killed until DML is performed.
[oracle@ol7-112-dg1 ~]$ kill -9 16428
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 16428
Session ID: 5 Serial number: 101
SQL>
Another test
--- Session Info
SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
2 where a.addr = b.paddr
3 and b.audsid = userenv('sessionid')
4 and b.sid=userenv('sid')
5 ;
SID SERIAL# PROCESSID CLIENTPID
---------- ---------- ------------------------ ------------------------
5 103 16533 16532
SQL>
--- From another session, check waits for above session
SQL> r
1 select NVL(s.username,'(oracle)') AS username, s.sid, s.serial#,
2 sw.event, sw.seconds_in_wait, sw.state
3 from v$session_wait sw, v$session s
4 where s.sid = sw.sid and s.sid=&sid
5*
Enter value for sid: 5
old 4: where s.sid = sw.sid and s.sid=&sid
new 4: where s.sid = sw.sid and s.sid=5
USERNAME SID SERIAL# EVENT SECONDS_IN_WAIT STATE
--------------- ---------- ---------- ------------------------------ --------------- -------------------
SYS 5 115 SQL*Net message from client 169 WAITING
SQL>Kill OS process using sqlplus CLIENTPID – immediate feedback –
[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16532
oracle 16532 8573 0 03:46 pts/0 00:00:00 sqlplus as sysdba
oracle 16533 16532 0 03:46 ? 00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 16557 11677 0 03:47 pts/1 00:00:00 grep --color=auto 16532
[oracle@ol7-112-dg1 ~]$
[oracle@ol7-112-dg1 ~]$ kill -9 16532
SQL> Killed
[oracle@ol7-112-dg1 ~]$Hopefully you will never have to kill your own session.
When you need kill your session, it’s better to have the correct information versus guessing.
