• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

HowTos | Basics | Concepts

  • Solaris
    • Solaris 11
    • SVM
    • ZFS
    • Zones
    • LDOMs
    • Hardware
  • Linux
    • CentOS/RHEL 7
    • RHCSA notes
    • SuSE Linux Enterprise
    • Linux Services
  • VCS
    • VxVM
  • Interview Questions
  • oracle
    • ASM
    • mysql
    • RAC
    • oracle 12c
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Hadoop
    • Hortonworks HDP
      • HDPCA
    • Cloudera
      • CCA 131

Oracle SQL script to Show current Users and SQL being Executed

By admin

Purpose of the script

Sample SQL script to show the following information.

  1. Who is currently logged on.
  2. If they are active, what SQL they are currently executing.
  3. How long they have been executing it. (last_call_et)

The script

Below is the SQL script:

column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status
from v$session
where username is not null
/

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;
/

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,
       module,
       action,
       client_info
from v$session
where module||action||client_info is not null;

Sample output from above SQL script

Below is the sample output from the above sql script.

SQL> @showsql

USERNAME               SID    SERIAL# PROCESS   STATUS
--------------- ---------- ---------- --------- ----------
SYS                      8       1011 5128      ACTIVE
SYSTEM                   9        101 3148      INACTIVE
SCOTT                   12       1962 5405      INACTIVE
--------------------
SYS(8,1011) ospid = 5128 program = sqlplus@stsun7 (TNS V1-V3)
Tuesday   10:03  Tuesday   10:15 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_

USERNAME        MODULE          ACTION          CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SYSTEM(9,101)   SQL*Plus
SCOTT(12,1962)  SQL*Plus

Filed Under: oracle

Some more articles you might also be interested in …

  1. How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace
  2. Oracle RMAN: Monitoring Recovery Manager Jobs
  3. Oracle 11g new feature – ASM Fast Rebalance
  4. How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )
  5. How to Failover a Service During Instance Shutdown Using SRVCTL
  6. How to Split a Partition Into Multiple Partitions in Oracle 12c
  7. How to extend ASM disk from OS level in CentOS/RHEL
  8. How to backup and delete archivelogs older than # number of days
  9. Oracle Database : Shutdown Basics (How to Shutdown Oracle Database)
  10. How to Backup and Restore Java Classes and Privileges only in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • How to change the default IP address of docker bridge
  • “su: Authentication failure” – in Docker
  • How to Pause and Resume Docker Containers
  • How to find docker storage device and its size (device mapper storage driver)
  • Understanding “docker stats” Command Output
  • Archives
  • Contact Us
  • Copyright

© 2019 · The Geek Diary