Thursday, December 3, 2009

How to display background processes,remote, local user processes in oracle 10g database.

How to display oracle background processes,remote, local user processes in oracle 10g database at shell command prompt:

Occasionally we may require to see the background processes, remote, local users processes started against oracle database in server system and the way to find those is below.

The way to find is different from unix systems to windows system, but in unix it is just running PS commond and which shows everything.

On unix , use the ps command to display them.

ps -ef |grep ora10g -- ora10g is the databasename or SID

you would see many ora__ora10g, these are all background processes.

How to see remote user processes:

Any process ID that include (LOCAL=NO) ,which is an indication that the server process is servicing a remote user process.

How to see local user processes:

Any process ID that has (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beg))), which is an indication that the user process is running on the same db server machine which means it did not come via listener.

On windows, server processes are threads within the single oracle.exe process and can be seen within windows task manager.

The following query can be used to list out all the processes/sessions started in database.

SELECT ses.sid,ses.serial#,pro.spid,ses.osuser,ses.type,
ses.username,
ses.program
FROM v$process pro,
v$session ses
WHERE pro.addr = ses.paddr;

Note: there is no username shown for backgrund processs and it is normal.

The best way to kill sessions which I feel should be incase of heavy load on the dB system very rarley or if any user request dba to do so most of the cases in development environment, the best way i see is to login into dB system and kill those using
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' -- best way
OR
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

KILLING ORACLE USER PROCESSES AT UNIX COMMAND PROMPT:
oracle spid can be found using ps -ef | grep ora command
$ kill spid

IN WINDOWS COMMAND PROMPT:

orakill sid spid

sid,Spid can be taken from running above select statement.
Note: however killing oracle sessions at OS command prompt is NOT suggestable as it may crash database systems.. so please avoid it all the time... always login into dba sql plus command prompt to do dba activities...

-- cheers...

No comments:

Post a Comment