To check all background process in oracle we can see using v$bgprocess view. I want to check which background process are active. Can anybody please help ?
Thanks
4 Answers
17:31:15 SYSTEM@sandbox> ed
Wrote file S:\spool\sandbox\BUFFER_SYSTEM_34.sql
1 select sid, process, program
2 from v$session s join v$bgprocess using (paddr)
3 where s.status = 'ACTIVE'
4* and rownum < 5
17:31:21 5 /
SID PROCESS PROGRAM
---------- ------------------------ ----------------------------------------------------------------
2 1332 ORACLE.EXE (PMON)
3 480 ORACLE.EXE (PSP0)
4 976 ORACLE.EXE (VKTM)
5 992 ORACLE.EXE (GEN0)
Elapsed: 00:00:00.05
However, this doesn't make much sense as they're always stated as active, so I guess you should define what you mean saying active.
-
thanks but just want to know why you have given "rownum < 5" in last ? Here active means only "active background processes" . Some are sometimes sleeping and wakeup periodically (as smon,pmon)– user2331048Commented Apr 29, 2013 at 9:57
-
3The
ROWNUM < 5is clearly there to avoid having an unnecessarily long listing in the answer. You also overestimate the amount of information available from inside the database. The Oracle code has to be run on many platforms, so there is (sensibly) a limit on what the amount of OS specific stuff they include. From the database perspective SMON is active regardless on whether it is asleep or awake. If you want to know what it is actually doing right now then you must turn to your OS tools.– APCCommented Apr 29, 2013 at 10:07
- from the top menu select "View" then "Task Progress". it will display back the progress window.
SQL>select program from v$process where background=1;
PROGRAM
------------------------------------------------
oracle@node1 (PMON)
oracle@node1 (PSP0)
oracle@node1 (VKTM)
oracle@node1 (GEN0)
oracle@node1 (DIAG)
oracle@node1 (DBRM)
oracle@node1 (DIA0)
oracle@node1 (MMAN)
oracle@node1 (DBW0)
oracle@node1 (LGWR)
oracle@node1 (CKPT)
PROGRAM
------------------------------------------------
oracle@node1 (SMON)
oracle@node1 (RECO)
oracle@node1 (MMON)
oracle@node1 (MMNL)
oracle@node1 (W000)
oracle@node1 (QMNC)
oracle@node1 (Q000)
oracle@node1 (SMCO)
oracle@node1 (CJQ0)
oracle@node1 (Q001)
21 rows selected.
select *
from v$session ses
join v$sql sql on
sql.ADDRESS = ses.SQL_ADDRESS
where 1=1
and upper(ses.OSUSER) like '%USERNAME%'
;
STATUS = ACTIVE if the session is running.
If the client logged off, the entry will dissappear after some time.
