ORA-00028

This example shows how to kill a user session. You can view sessions in your database as follows:

SQL> col username format a10
SQL> l
  1  select username, sid, serial#, status
  2  from v$session
  3* where username = 'ANDREW'
SQL> /

USERNAME          SID    SERIAL# STATUS
---------- ---------- ---------- --------
ANDREW            143         79 INACTIVE

SQL>

You can then use the SID and SERIAL# displayed to kill a session as shown below. The username you are interested in may have more than one session. If this happens, you will need to use one or more of the other columns in V$SESSION (e.g.OSUSER, MACHINE, LOGON_TIME etc) to see exactly which session you want to get rid of:

SQL> alter system kill session '143,79';
 

System altered.
 

SQL>

The killed session stays in V$SESSION with a status of KILLED:

SQL> select username, sid, serial#, status
  2  from v$session
  3  where username = 'ANDREW';

USERNAME          SID    SERIAL# STATUS
---------- ---------- ---------- --------
ANDREW            143         79 KILLED

SQL>

Until the user tries to access it again (the connection was made earlier). He will then get an ORA-00028:

SQL> conn andrew/reid@test10
Connected.
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL>

... and the session will disappear from V$SESSION:

SQL> select username, sid, serial#, status
  2  from v$session
  3  where username = 'ANDREW';

no rows selected

SQL>

ORA-00030

This was tested on Oracle 11.2. When you run ALTER SYSTEM KILL SESSION ‘X,Y’; X must be a SID from V$SESSION and Y must be the SERIAL# from the same row. If not, you will get an ORA-00030: 

SQL> select count(*) from v$session

  2  where sid = 123

  3  and serial# = 321

  4  /

 

  COUNT(*)

----------

         0

 

SQL> alter system kill session '123,321'

  2  /

alter system kill session '123,321'

*

ERROR at line 1:

ORA-00030: User session ID does not exist.

 

SQL>

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>

ORA-04021

A colleague had another problem with a package compilation hanging in an Oracle 11.1.0.6.0 test database. I was able to reproduce it as follows: 

SQL> alter package srce.pk_pricing compile

  2  /

alter package srce.pk_pricing compile

*

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock object

 

SQL>

 

There were locks on this package according to V$DB_OBJECT_CACHE but this time, flushing the shared pool made no difference and I found that I still could not compile the package:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          3

PACKAGE                               3

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4  and name = 'PK_PRICING'

  5  /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          3

PACKAGE                               3

 

SQL>

 

I read somewhere that you could not compile a package if somebody was using it and you could find who it was by looking in V$ACCESS. I joined it with V$SESSION to pick up the SERIAL# as follows:

 

SQL> l

  1  select a.sid, serial#

  2  from v$access a, v$session b

  3  where a.sid = b.sid

  4* and object = 'PK_PRICING'

SQL> /

 

       SID    SERIAL#

---------- ----------

       187       7623

       225       3111

       179       6987

 

SQL>

 

I killed the first session:

 

SQL> alter system kill session '187,7623'

  2  /

 

System altered.

 

SQL>

 

… and the number of locks in V$DB_OBJECT_CACHE went down:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          2

PACKAGE                               2

 

SQL>

 

I killed the other two sessions:

 

SQL> alter system kill session '225,3111'

  2  /

 

System altered.

 

SQL> alter system kill session '179,6987'

  2  /

 

System altered.

 

SQL>

 

The number of locks in V$DB_OBJECT_CACHE went to zero:

 

SQL> l

  1  select type, locks

  2  from v$db_object_cache

  3  where owner = 'SRCE'

  4* and name = 'PK_PRICING'

SQL> /

 

TYPE                              LOCKS

---------------------------- ----------

PACKAGE BODY                          0

PACKAGE                               0

 

SQL>

 

… and I was able to compile the package in a second or two:

 

SQL> alter package srce.pk_pricing compile

  2  /

 

Package altered.

 

SQL>

If this does not work for you, click on the Older Post link below to see what I did the first time this happened. 

ORA-00027

In the SQL*Plus session below, I connect as SYS to an Oracle 10 database. Then I show that there is only one user logged on as SYS i.e. me. I note the sid and serial# and try to use them to kill my current session. Oracle does not allow this and displays an ORA-00027:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select sid, serial# from v$session
  2  where username = 'SYS'
  3  /

       SID    SERIAL#
---------- ----------
       159          5

SQL> alter system kill session '159,5';
alter system kill session '159,5'
*
ERROR at line 1:
ORA-00027: cannot kill current session

SQL>