select sid,serial#,STATUS,to_char(logon_time,'DD-MON_YY   HH24:MI:SS') from v$session where username like '%&usern%' ORDER BY 4 DESC;
       533       2234 INACTIVE     04-MAR_10   01:59:02
       525       5919 ACTIVE       04-MAR_10   01:54:50
       534       4186 INACTIVE     04-MAR_10   01:05:15
       520       2320 INACTIVE     04-MAR_10   00:29:44
       511       2535 INACTIVE     03-MAR_10   14:23:04
      1083       5562 INACTIVE     03-MAR_10   14:18:45
       513       2231 INACTIVE     03-MAR_10   14:15:16
      1091       6881 INACTIVE     03-MAR_10   14:11:28
      1070       5521 INACTIVE     03-MAR_10   13:42:22

select sql_id, sql_text 
from v$sql 
 where sql_id in (
           select sql_id 
           from v$active_session_history 
           where session_id=511
           and sample_time=
                 (select max(sample_time) 
                   from   v$active_session_history 
                   where session_id=&SID 
                 )
           );
Enter value for sid:511
d5jybz8k3sk17
SELECT B.COUNTRY , B.STATE FROM PS_BUS_UNIT_TBL_BI A , ..

select sid, username, terminal, osuser,
        t.start_time, r.name, t.used_ublk "ROLLB BLKS",
        decode(t.space, 'YES', 'SPACE TX',
           decode(t.recursive, 'YES', 'RECURSIVE TX',
              decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
        )) status
 from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
 where t.xidusn = r.usn
   and t.ses_addr = s.saddr;

/
       511 EP75     pts/5                          fs89dmo  03/03/10 18:00:54 _SYSSMU1
                                                                              0$

      1070 EP75     pts/5                          fs89dmo  03/04/10 02:05:56 _SYSSMU9
                                                                              $

column username format a20
column sql_text format a55 word_wrapped
 
set serveroutput on size 1000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||
                ') ospid = ' ||  process ||
                ' program = ' || program username,
             to_char(LOGON_TIME,' Day HH24:MI') logon_time,
             to_char(sysdate,' Day HH24:MI') current_time,
             sql_address, LAST_CALL_ET
        from v$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null order by last_call_et ) loop
         
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null)) sql_text
                     from v$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4) loop
                      
            if ( y.sql_text not like '%listener.get_cmd%' and
                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%') then
                 
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' ||
                                      x.current_time||
                                      ' last et = ' ||
                                      x.LAST_CALL_ET);
                dbms_output.put_line(
                          substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;
    end loop;
end;
/

EP75(1070,5521) ospid = 6202 program = psae@corpdevapp11 (TNS V1-V3)
Wednesday 13:42  Thursday  03:30 last et = 0
INSERT INTO PS_ITEM_LINES_TBL (SETID, ENTRY_TYPE, EFFDT, ENTRY_REASON, DST_SEQ_NUM, DST_LINE_PERCENT, DEBIT_CREDIT,
ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE,
AFFILIATE_INTRA1, AFFILI
--------------------
EP75(533,2234) ospid = 4840:5720 program = sqlplus.exe
Thursday  01:59  Thursday  03:30 last et = 0
SELECT USERNAME||'('||SID||','||SERIAL#|| ') ospid = ' || PROCESS || ' program = ' || PROGRAM USERNAME,
TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME, TO_CHAR(SYSDATE,' Day HH24:MI') CURRENT_TIME, SQL_ADDRESS, LAST_CALL_ET
FROM V$SESSION WHERE STATUS

EP75(1070,5521) ospid = 6202 program = psae@corpdevapp11 (TNS V1-V3)
Wednesday 13:42  Thursday  04:16 last et = 2
UPDATE PS_PENDING_ITEM SET STATE_SHIP_FROM = ( SELECT V.STATE FROM PS_CUST_ADDRESS V WHERE V.SETID = ( SELECT SETID FROM
PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = PS_PENDING_ITEM.BUSINESS_UNIT AND RECNAME = 'CUST_ADDRESS') AND V.CUST_ID =
PS_PENDING_ITE