5 Replies Latest reply: Oct 23, 2009 12:52 AM by aaaaa RSS

    How to find session

    aaaaa
      Hi;
      I have 10.2.0.1 on sun. I am using toad. while i try to check session(Database>>Monitor>> Session Browswer) i see only my session is active, i even cant see in locks tab any session.

      While i am using prstat comman i see one oracle user (X) running and its using cpu. I use this sql to find who and what happening

      select s.sid,s.serial#,s.username,s.osuser,p.spid,p.terminal,s.program from v$session s ,v$process p where paddr=addr and spid="UNIX_PID_NO"

      it returns nothing to me

      How i can find this oracle user doing what?and why i cant see his session in toad?

      Any idea

      Thanks alot
        • 1. Re: How to find session
          asifkabirdba
          -- show PROCESS id for all the active sessions

          select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser
          from V$process p,V$session s
          where s.paddr = p.addr and s.status = 'ACTIVE' and s.username not like '%SYS%';


          Hope this helps.


          Reards
          Asif Kabir
          • 2. Re: How to find session
            Srini Chavali-Oracle
            Does the user you are using to connect in TOAD have DBA privileges to see all sessions ?

            HTH
            Srini
            • 3. Re: How to find session
              sb92075
              from WINDOW#!
              bcm@bcm-laptop:~$ ps -ef | grep -i sqlplus
              bcm       4794  3635  0 07:45 pts/0    00:00:00 sqlplus
              bcm       9706  9689  0 09:53 pts/1    00:00:00 grep -i sqlplus
              bcm@bcm-laptop:~$ ps -ef | grep 4794
              bcm       4794  3635  0 07:45 pts/0    00:00:00 sqlplus
              oracle    4823  4794  0 07:45 ?        00:00:03 oraclev102 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
              bcm       9708  9689  0 09:53 pts/1    00:00:00 grep 4794
              From WINDOW#2
              SQL> select s.sid,s.serial#,s.username,s.osuser,p.spid,p.terminal,s.program from v$session s ,v$process p where paddr=addr and spid=4823;        
              
                     SID    SERIAL# USERNAME
              ---------- ---------- ------------------------------
              OSUSER                      SPID         TERMINAL
              ------------------------------ ------------ ------------------------------
              PROGRAM
              ------------------------------------------------
                     159         3 SYS
              bcm                      4823         pts/0
              sqlplus@bcm-laptop (TNS V1-V3)
              Works for me!
              • 4. Re: How to find session
                692600
                where paddr=addr and spid="UNIX_PID_NO"


                spid is number, take away the double quotes
                • 5. Re: How to find session
                  aaaaa
                  Hi;

                  Sorry for lately response,
                  Sirini;

                  I follow this and create user for toad and connect toad wiht this user and used toad for monitor session,so i guess i have all privilages for see session

                  sqlplus "/as sysdba"

                  create role xxx_role_dba ;

                  grant dba to xxx_role_dba ;

                  grant xxx_role_dba to user1 identified by user1;

                  Should i need any other grant for user1?

                  Sb;
                  My top command return me (oracle is Oracle_home owner,zzzz is database user)

                  PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
                  550 oracle 9688K 5400K sleep 1 0 0:00:00 0.0% sshd/1
                  22929 zzzz 91M 88M sleep 56 0 0:00:22 0.0% ZZZ/collector/29


                  ps -ef| grep zzzz returns me

                  oracle 12892 1 0 Oct 12 ? 0:12 ora_qmnc_zzzz
                  oracle 14316 1 0 Oct 12 ? 0:07 ora_q001_zzzz
                  oracle 12856 1 0 Oct 12 ? 3:12 ora_mmon_zzzz
                  oracle 12850 1 0 Oct 12 ? 1:13 ora_smon_zzzz
                  oracle 12842 1 0 Oct 12 ? 2:31 ora_dbw0_zzzz
                  oracle 12846 1 0 Oct 12 ? 3:01 ora_lgwr_zzzz
                  oracle 12852 1 0 Oct 12 ? 0:01 ora_reco_zzzz
                  oracle 12838 1 0 Oct 12 ? 1:15 ora_psp0_zzzz
                  oracle 12840 1 0 Oct 12 ? 0:42 ora_mman_zzzz
                  oracle 12836 1 0 Oct 12 ? 9:02 ora_pmon_zzzz
                  oracle 12848 1 0 Oct 12 ? 8:30 ora_ckpt_zzzz
                  oracle 13772 1 0 Oct 12 ? 0:01 ora_q000_zzzz
                  oracle 22935 1 0 07:23:23 ? 0:00 oraclezzzz (LOCAL=NO)

                  if i use yoru query its gives me result(SID etc..) but if i use in toad .. From menu >> Database >>monitor>> session browser this SID which return me by your query seems INACTIVE but it still there in top commend if i retype TOP

                  Any idea?