Interesting addition in 11G

From: Mladen Gogala <gogala.mladen_at_bogus.email.com>
Date: Wed, 10 Jun 2009 10:26:52 +0000 (UTC)
Message-ID: <h0o1pc$9f1$1_at_solani.org>

Once upon a midnight dreary, while I pondered weak and waery, I was browsing the SQL reference, looking for something about the ALTER SYSTEM command when I suddenly looked at the "KILL SESSION" option: http://tinyurl.com/ltcpfs
Now, everybody knows what alter system kill session does:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; In oracle 11G, the fine manual says this:

KILL SESSION Clause

<b>The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. To use this clause, your instance must have the database open. Your session and the session to be terminated must be on the same instance unless you specify integer3.You must identify the session with the following values from the V$SESSION view:

*

      For integer1, specify the value of the SID column.
*

      For integer2, specify the value of the SERIAL# column.
*

      For the optional integer3, specify the ID of the instance where the target session to be killed exists. You can find the instance ID by querying the GV$ tables.</b>

So, there is an optional integer 3, signifying the instance? OK, let's give it a try:

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID SID SERIAL#
---------- ---------- ----------

         1 130 620

SQL> alter system kill session '130,620,1';  alter system kill session '130,620,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

Whoa! Of course, the omnipotent and the omnipresent Google knows everything! Googling turned out Miladin Modrakovic's blog, with the solution:

http://oraclue.com/2009/05/18/procedure-to-kill-blocking-session-in- rac-11g/

Now, it works:

SQL> alter system kill session '130,620,_at_1';

System altered.

SQL> So, the difference is in the non-documented '_at_' character! I don't know whether I should be amused because of the undocumented addition to the syntax making it invalid or should I be happy about this very useful new option. God bless Miladin, his site is so jam packed with the internalia that I will probably spend the rest of the month studying it. For those who have read Asimov, I am still googling to find out how to reverse entropy.

-- 
http://mgogala.freehostia.com

Received on Wed Jun 10 2009 - 05:26:52 CDT