Hello everybody,

does anybody know what kind of process the "oracle<SID>"-Process is, e.g.

ps -ef :

oracleBAANDB (DESCRIPTION=(LOCAL=YES(SDU=8192)(TDU=8192)(ADDRESS=(PROTOCOL=beq

There seems to be a "oracle<SID>"-process for every Baan-session, but I can not find the connection between the bshell and the "oracle<SID>"-process, because the PPID is always 1 , i.e. the init-process. I also cannot find a connection between this process and the Oracle session ID/serial.

Sometimes some of these processes seem to hang and block CPU-time but how can I find a way to identify the corresponding Baan-Session ? If there is none, can this process be terminated ?
How do you handle this ?

Thank you.

Mario

but I can not find the connection between the bshell and the "oracle<SID>"-process, because the PPID is always 1
Owner of the bshell and the "oracle<SID>"-process is the same user.

I think this is what you are looking for:

SQL> l
1 select s.process
2 from v$process p, v$session s
3 where p.pid = s.sid
4* and p.spid = '9475'
SQL> /

PROCESS
---------
9473

$ ps -ef |/usr/xpg4/bin/grep -E '9473|9475' |grep -v grep
dwesten 9473 9472 0 13:50:56 ? 0:00 oracle8 (dwesten:9472/PIPE<dwesten@bobbie:9471>) 9472 321 11 12
oracle 9475 1 0 13:50:56 ? 0:00 oracleCF (DESCRIPTION=(LOCAL=YES)(SDU=8192)(TDU=8192)(ADDRESS=(PROTOCOL=beq)))


Of course it can be extended as you like.

Hope this helps,
Dick

Mario,

The process you refer to is a Database Thread spawned by the Oracle Database.

This is the process used for the Oracle 'session' - triggered by communications from the bshell via the oracle driver.

Indeed, this process can get busy, consume CPU and even hang sometimes.

To identify the session status within the database, find the unix process id and run:

SQL> select username, status, process, program from v$session where process = 12345;

An ACTIVE status will indicate the DML/DDL statements are still running within the database. Query v$sqltext or v$sql to find the active SQL code.

Indeed, use Dikkie's code above to trace this Oracle thread back to the Baan bshell & oracle driver.

If the Oracle thread is completely hung, the recommended way to terminate this process is to kill the Oracle Session from within the database.

SQL> select sid, serial# from v$session where process =12345;

16 8

SQL> alter system kill session "16,8";

The status in v$session will then be changed to KILLED. And after a short period the unix process should terminate.

If the unix process is still hanging after this, then a unix kill can then be used on that process.

Have fun.

James

YES !!!

You saved my day.

Thank you.

M.

Hi Respected James,

You wrote:
----------------------------
If the unix process is still hanging after this, then a unix kill can then be used on that process.
----------------------------

My Query:
If the NT process is still hanging after this, how can we kill the process?
If we tried Task Manager > Process > End Process > Warning > Yes, it give the error "Unable to terminate process, The operation could not be completed, Access is denied."

Any idea about NT?

-Suhas

Hi Suhas,

I'm no NT expert - though by the sounds of it you're a bit stuck.

You could try restarting the database service - but even that may not free the process. If the process is causing a problem, you may end up having to reboot.

One of the many major drawbacks and un-popularities of NT. Thought about Linux?

James

The NT resource kit has a utility called 'kill'. This one should help you to really kill the process.

Hope this helps,
Dick

Hi,

I got the following script from Oracle site -

http://otn.oracle.com/oramag/code/tips2003/030903.html

First, you can find out which Oracle process has hung-up by the folloing script -

set pagesize 100
set linesize 100
column Program format a20
SELECT s.Sid AS "Sid",
s.Serial# AS "Serial#",
p.spid AS "ThreadID",
s.OSUser AS "OSUser",
s.Program AS "Program"
FROM V$Process p, V$session s
WHERE p.addr=s.paddr(+)
ORDER BY s.Sid

Sample Output:

Sid Serial# ThreadID OSUser Program
--- ------- -------- ------ -------
...
10 809 102 User01 Prog.exe
11 345 333 SeUser App.exe
15 719 422 User01 Prge.exe
20 345 123 SeUs Acco.exe
555
324

Then use the following command from a command prompt to remove the threads, i.e. killed session:

"orakill DatabaseInstanceName ThreadNumber"

example

"orakill ORCL 555"

example

"orakill ORCL 324"