History about an active SQL statement

How to check whether the update/delete/drop statement is running or not Example
The below script is running from long time,so by using the below steps we can check the progress of the statement

update mtl_material_transactions
set costed_flag = 'N',
transaction_group_id = NULL,
transaction_set_id = NULL
where costed_flag = 'E' or costed_flag = 'N';

On DB node run the below command to check the session SID

col module for a20
select inst_id,sid,module,process,status from gv$session where  status ='ACTIVE'  and username ='APPS' ;

Example

SQL> col module for a20
select inst_id,sid,module,process,status from gv$session where  status ='ACTIVE'  and username ='APPS' ;SQL>

INST_ID        SID    MODULE               PROCESS      STATUS

1             2046    FNDGSCST                   5564         ACTIVE
1             2073    WSHINTERFACE         17385        ACTIVE
1             2096    SQL*Plus                       31927        ACTIVE

1       2096 SQL*Plus             31927        ACTIVE  ==>this is the session for the running statement

Execute the below command to confirm the running statement with the SID from above command

SQL> select SQL_TEXT from v$sqltext where ADDRESS in (select SQL_ADDRESS from v$session where SID=2096) order by piece;

SQL_TEXT
----------------------------------------------------------------
update mtl_material_transactions set costed_flag = 'N', transact
ion_group_id = NULL, transaction_set_id = NULL where costed_flag
= 'E' or costed_flag = 'N'

SQL>

Execute the below command to check the session is active or not

REM checking Timing details, Client PID of associated oracle SID
REM ============================================================
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

Example

SQL> REM checking Timing details, Client PID of associated oracle SID
REM ============================================================
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ........................
SQL> Details of SID / SPID / Client PID
SQL> ==================================
SQL>    ......................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMAN  AME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));  
Enter value for sid: 2096
Enter value for spid:
Enter value for clientpid:

Session  Id.............................................: 2096
Serial Num..............................................: 26063
User Name ..............................................: APPS
Session Status .........................................: ACTIVE
Client Process Id on Client Machine ....................: *31927*
Server Process ID ......................................: 3498
Sql_Address ............................................: 000000023C9B9FC0
Sql_hash_value .........................................: 3476221953
Schema Name ..... ......................................: APPS
Program  ...............................................: sqlplus@test15 (TNS V1-V3)
Module .................................................: SQL*Plus
Action .................................................:
Terminal ...............................................: pts/8
Client Machine .........................................: test15
LAST_CALL_ET ...........................................: 2689
S.LAST_CALL_ET/3600 ....................................: .746944444444444444444444444444444444444
SQL>

Session Status is Active