Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about the conversation and process of Kill session and orakill, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

A user process occasionally suspends or consumes too many resources and rejects other sessions. If DBA still has access to the database, she can usually issue the following query:

Select s.username, s.osuser, s.sid, s. Serializations, p.spid from v$session spark vested process p

Where s.paddr = p.addr and s.username is not null

Select 'alter system kill session','| | trim (t2.sid) | |','| | trim (t2.serial#) | |'';'

From v$locked_object T1 recording session T2 where t1.session_id=t2.sid order by t2.logon_time

This query returns the database user name, operating system user name, session ID, serial number, and system process ID (SPID). The DBA user can then issue the following command (the information returned by the previous query using SID and SERIAL#):

ALTER SYSTEM KILL SESSION 'sid,serial#'

ALTER SYSTEM KILL SESSION '9203'

There are two problems with using this statement.

First: any locks or resources assigned to this process will not be released until the session times out completely.

Second: queries and kill commands need to be able to access the database. If one gets out of control, there may be problems with database access.

In a UNIX database, the next step is to locate the process in the UNIX prompt output from the ps command (also looking for ID such as OSUSER and SPID) and then use kill-9 spid to end the runaway background process. However, in Windows, there is only one process ORACLE.EXE, and the user connection is handled in the Windows thread, not in the process. If you use the Windows task manager to end the Oracle thread, it is possible to affect all users and background threads and cause the database to crash.

For these reasons, Oracle provides an orakill.exe command in the Oracle Home/bin directory with the same parameters as ALTER SYSTEM KILL SESSION, but does not require a database connection. To locate a particular thread, you need to look for a program that displays all threads belonging to a process. The Windows Task Manager can only display the number of threads and processes. You need to find a tool for Windows 2000 and NT from Microsoft's resource kit, such as free QuickSlice, or Qslice.exe (the tool is based on Windows), or PStat (Pstat.exe is a command line tool). Simply enter the thread ID (in decimal) and SID after the orakill command:

Orakill

Orakill ORCL 2760

"Kill of thread id 2760 in instance ORCL successfully signalled [sic]."

You should use orakill only if you cannot access the database to perform ALTER SYSTEM KILL SESSION. If you accidentally end a necessary background process, such as PMON, it is likely to cause the database to crash. Beginners should never do this.

The usage of Orakill is as follows:

At the Dos prompt: > orakill sid thread

Description: Sid number of sid Oracle

Thread id number of thread Oracle

You can query the thread number of Oracle in the Sql*plus tool.

Sql: > Select p.spid THREADID, s.osuser, s.program

From v$process p, v$session s

Where p.addr = s.paddr

The results are as follows:

THREADID OSUSER PROGRAM

-

169 SYSTEM ORACLE.EXE

215 SYSTEM ORACLE.EXE

280 SYSTEM ORACLE.EXE

267 SYSTEM ORACLE.EXE

287 SYSTEM ORACLE.EXE

288 SYSTEM ORACLE.EXE

271 SYSTEM ORACLE.EXE

282 SYSTEM ORACLE.EXE

239 PROD_NTdjones SVRMGRL.EXE

281 SSMITH-PCssmith SQLPLUSW.EXE

12 rows selected.

It is important to note that if you Kill the core background thread of Oracle (DBWR, LGWR, SMON or PMON), it will cause the Oracle instance to shut down. Check the core background thread of Oracle as follows:

Sql: > Select vb.name NOME, vp.programe PROCESSNAME, vp.spid THREADID, vs,sid SID

From v$session vs, v$process vp, v$bgprocess vb

Where vb.addr '00' and

Vb.paddr = vp.addr and

Vp.addr = vs.paddr

The query results are as follows:

NOME PROCESSNAME THREADID SID

-

PMON ORACLE.EXE 169 1

DBW0 ORACLE.EXE 215 2

LGWR ORACLE.EXE 280 3

CKPT ORACLE.EXE 267 4

SMON ORACLE.EXE 287 5

RECO ORACLE.EXE 288 6

SNP0 ORACLE.EXE 271 7

SNP1 ORACLE.EXE 282 8

8 rows selected.

Window xp + oracle 9.2.0.1

=

The usage of orakill

=

SQL > SELECT spid, osuser, s.programmer sid

FROM v$process p, v$session s

WHERE p.addr=s.paddr

SPID OSUSER PROGRAM SID

-

6928 SYSTEM ORACLE.EXE 1

5272 SYSTEM ORACLE.EXE 2

7008 SYSTEM ORACLE.EXE 3

6588 SYSTEM ORACLE.EXE 4

6780 SYSTEM ORACLE.EXE 5

6128 SYSTEM ORACLE.EXE 6

6740 SYSTEM ORACLE.EXE 7

6684 SYSTEM ORACLE.EXE 8

7092 SYSTEM ORACLE.EXE 9

6272 SYSTEM ORACLE.EXE 10

4760 lifeng.fang sqlplus.exe 12

SPID OSUSER PROGRAM SID

-

6484 lifeng.fang sqlplus.exe 11

4284

Syntax: orakill instance name spid (thread number on win)

SQL > host orakill charset 6484

Kill of thread id 6484 in instance charset successfully signalled.

Spid is the os process ID

Pid is Oracle process ID.

According to Gai Guoqiang's guess

After kill session in Oracle, Oracle simply points the paddr of the relevant session to the same virtual address. At this point, v$process and v$session lose their connection, and the process is interrupted. Then Oracle waits for PMON to clear the Session. So it usually takes a long time to wait for a Session exit marked Killed. If you try to execute the task again by the process of Kill at this time, you will immediately receive a prompt that the process is interrupted and process exits, and Oracle will immediately start PMON to clear the session. This is treated as an exception interrupt.

Readers can refer to specific experimental examples.

Http://www.eygle.com/faq/Kill_Session.htm

Of course, if the user is no longer in use, for example, when the user decides to delete the user, PMON will probably not automatically clean up the session, then the PMON execution needs to be triggered manually.

First confirm that the PMON process is who

SQL > select pid,spid from v$process pr é cor vault bgprocess b

Where b.paddr=p.addr

And name='PMON'

PID SPID

--

2 3608

SQL > oradebug wakeup 2

Processed statement

SQL > select p.addr from v$process p where pid 1 minus select s.paddr from v$session s; query the address of the process that has been KILL

ADDR-

542B70E8

542B7498

After reading the above, do you have any further understanding of the conversation and process of Kill session and orakill? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.