Tuesday, November 26, 2013

ORA-00026

This was tested on Oracle 9. When you use the ALTER SYSTEM KILL SESSION command, you must supply the SID and SERIAL# from an entry in V$SESSION. You get an ORA-00026 if you do not provide one: 

SQL> alter system kill session
  2  /
alter system kill session
                        *
ERROR at line 1:
ORA-00026: missing or invalid session ID
 
SQL>
 
The SID and SERIAL# are both numbers so you also get an ORA-00026 if you give non numeric values:
 
SQL> alter system kill session 'A,B'
  2  /
alter system kill session 'A,B'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID
 
SQL>
 
… or values which do not come from V$SESSION:
 
SQL> select count(*) from v$session
  2  where sid = 123
  3  and serial# = 456
  4  /
 
  COUNT(*)
----------
         0
 
SQL> alter system kill session '123,456'
  2  /
alter system kill session '123,456'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID
 
SQL>
 
However, if the values are valid, the command should work:
 
SQL> select sid, serial# from v$session
  2  where username = 'ANDREW'
  3  /
 
       SID    SERIAL#
---------- ----------
        13         34
 
SQL> alter system kill session '13,34'
  2  /
 
System altered.
 
SQL>

No comments: