5

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

2
  • 4
    What's wrong with querying that view you already found?
    – user1822
    Apr 29, 2013 at 7:02
  • What OS? Windows/Linux/AIX .... ?
    – Philᵀᴹ
    Apr 29, 2013 at 12:00

4 Answers 4

4
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.

2
  • 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)
    – user2331048
    Apr 29, 2013 at 9:57
  • 3
    The ROWNUM < 5 is 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.
    – APC
    Apr 29, 2013 at 10:07
2
  • from the top menu select "View" then "Task Progress". it will display back the progress window.
-1
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.
-2
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.

2
  • The OP is asking about background processes: these tend notr to show up as SQL executions.
    – APC
    Apr 29, 2013 at 10:02
  • Yeah, I mistook "active" meaning which sessions are currently running.
    – Devolus
    Apr 29, 2013 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.