Dołącz do nas!

Wszystkie znaki interpunkcyjne za wyjątkiem kropek, łączników, znaków podkreśleń i spacji są niedozwolone.
Adres e-mail, koniecznie prawidłowy. Będą nań przesyłane wszystkie wiadomości e-mail od systemu. Adres nie jest udostępniany publicznie i będzie wykorzystywany jedynie w wypadku prośby o przesłanie nowego hasła lub do przesyłania informacji o nowościach czy innych komunikatów.
CAPTCHA
To pytanie ma na celu ochronę przed automatycznym spamowaniem oraz ustalenie, czy odwiedzający stronę jest człowiekiem.
Image CAPTCHA
Wpisz kod widoczny powyżej.

Logowanie

Nazwa użytkownika witryny Project Envision.
Hasło powiązane z nazwą użytkownika.
Prześlij nowe hasło

Killing Sessions in Oracle

Sometimes Oracle Database Administrators needs to kill a sessions in Oracle Database. The best way to do it is to ALTER SYSTEM KILL SESSION statement. To use this statement we must provide SID and SERIAL# of the session we are going to kill.

We can find the SID and SERIAL# using below query:

SQL> select sid, serial#, username, osuser, status

2 from v$session
3 where username is not null
4 /

SID SERIAL# USERNAME OSUSER STATUS
170 6 U1 oracle INACTIVE
200 5 SYS oracle ACTIVE

SQL> alter system kill session '170,6'
2 /
System altered.

SQL> select sid, serial#, username, osuser, status
2 from v$session
3 where username is not null
4 /

SID SERIAL# USERNAME OSUSER STATUS
170 6 U1 oracle KILLED
200 5 SYS oracle ACTIVE

In user session, that was killed we will see the following when we try to execute the query
SQL> select * from dual
2 /
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

Sometimes Oracle is not able to kill the session immediately, in such cases the session is marked for kill. Oracle will kill that session as soon as possible.
User session cannot be interrupted while it is performing network I/O or rolling back transaction, hence there is a possibility to add IMMEDIATE parameter to ALTER SYSTEM KILL SESSION statement. IMMEDIATE parameter tells Oracle to terminate the session without waiting for outstanding activity to complete.

User Session:
SQL> conn u1/u1@orcl
Connected.
SQL> create table t100 as
2 select * from all_objects
3 where 1 = 0
4 /

Table created.

Admin Session:
SQL> select sid, serial#, username, osuser, status
2 from v$session
3 where username is not null
4 /

SID SERIAL# USERNAME OSUSER STATUS
171 29 U1 oracle INACTIVE
200 5 SYS oracle ACTIVE


User Session:
SQL> insert into t100
2 select * from all_objects
3 /

Admin Session
SQL> alter system kill session '171,29' immediate
2 /
System altered.
User Session
select * from all_objects
*
ERROR at line 2:
ORA-03113: end-of-file on communication channel
Process ID: 5449
Session ID: 171 Serial number: 29

Using ALTER SYSTEM KILL SESSION is suggested way of killing an Oracle session, however we can also kill the session from operating system level. On Windows NT we can use orakill program, and on Linux / Unix systems we can use kill -9 command. Before we use those commands we need to identify operating system process id, to do it we can use the following query.

SQL> select s.sid, s.serial#, s.username,s.status, s.osuser, p.spid

2 from v$session s, v$process p
3 where s.paddr = p.addr and s.username is not null
4 /

SID SERIAL# USERNAME STATUS OSUSER SPID
200 5 SYS ACTIVE oracle 4796
167 114 U1 INACTIVE oracle 6031

On Linux / Unix:
[oracle@localhost ~]$ ps aux | grep 6031 | grep -v grep
oracle 6031 0.0 1.1 599856 18308 ? Ss 01:42 0:00 oracleorcl (LOCAL=NO)
[oracle@localhost ~]$ kill -9 6031
In User Session
SQL> select * from dual
2 /
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6031
Session ID: 167 Serial number: 114

On Windows, orcl is instance SID and 4924 is spid selected using above query.
C:\Documents and Settings\mob>orakill orcl 4924 Kill of thread id 4924 in instance orcl successfully signalled.

Categories: