Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Script to get Current Sessions (SID,SPID) and SQLs for all Active Sessions

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    Script to get Current Sessions (SID,SPID) and SQLs for all Active Sessions

    set pagesize 100

    col c1 for a9
    col c1 heading "OS User"
    col c2 for a9
    col c2 heading "Oracle User"
    col b1 for a9
    col b1 heading "Unix PID"
    col b2 for 9999 justify left
    col b2 heading "SID"
    col b3 for 99999 justify left
    col b3 heading "SERIAL#"
    col sql_text for a35

    break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3

    select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
    a.sql_text
    from v$sqltext a, v$session b, v$process c
    where a.address = b.sql_address
    and b.status = 'ACTIVE'
    and b.paddr = c.addr
    and a.hash_value = b.sql_hash_value
    order by c.spid,a.hash_value,a.piece
    /

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    Script to get session details for a user (without sqltext)

    select
    sid,serial#,username,status,module,
    to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON", program,last_call_et
    from
    v$session
    where
    username like '%&usr%'
    order by logon_time;

+ Reply to Thread

Similar Threads

  1. Script to Monitor datapump sessions
    By Amit in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 07-15-2012, 07:53 PM
  2. Sessions sorted by logon time
    By devesh_ocp in forum Database Scripts
    Replies: 0
    Last Post: 04-24-2012, 10:39 PM
  3. Script to get active middle-tier user sessions
    By Amit in forum SCRIPTS : Users,Profiles,Applications,Responsibilities,Forms,JDBC,Patch,Versions,Products
    Replies: 0
    Last Post: 11-04-2011, 09:37 AM
  4. Replies: 0
    Last Post: 11-02-2011, 07:29 PM
  5. Script to find locks - blocking and waiting sessions
    By Hemant in forum Database Scripts
    Replies: 0
    Last Post: 11-02-2011, 07:21 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics