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
