Saturday, February 25, 2012

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>

September 2012:

A colleague recently bought a copy of the book advertised below. It explains ORA-00027 on page 729. I had a good look at the book today and can thoroughly recommend it. I will be featuring worked examples from it in due course:



2 comments:

Laurent Schneider said...

alter session set events 'immediate crash';

to kill yourself

Andrew Stuart Reid said...

Dear Laurent,

Thank you for your suggestion. I had not seen it before so I decided to try it out:

SQL> select sysdate from dual;

SYSDATE
---------
26-FEB-12

SQL> alter session set events 'immediate crash';
alter session set events 'immediate crash'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> select sysdate from dual;
ERROR:
ORA-03114: not connected to ORACLE

SQL>

Although I have seen things like alter session set events '10046 etc' on the Internet before, I do not understand what they are for. I will try to research them and write them up on my blog too.

Kind regards,

Andrew