2

I was looking in v$session_longops and found an operation currently being run and its corresponding SQL_ID

SELECT sql_id, sid, to_char(start_time,'hh24:mi:ss') stime, time_remaining,
message,( sofar/totalwork)*100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1
/
SQL_ID  SID STIME   TIME_REMAINING  MESSAGE PERCENT
381hwy1dbtntp   104 09:36:57    157 Index Fast Full Scan:  SOME_USER.SOME_TABLE: 12715 out of 36702 Blocks done 34.64388861642417306958748842024957767969
0htuhsqj193gf   184 09:15:21    4488    Table Scan:  SOME_USER.SOME_TABLE: 7028 out of 13568 Blocks done    51.79834905660377358490566037735849056604

When I look for the user session running this based on the SQL_ID I didn't find any session running the first one. (381hwy1dbtntp)

select distinct sql_id from v$session;

SQL_ID

fwxv8a99vkg9r
1cwb8j02491w0
gypnfv5nzurb0
0htuhsqj193gf
2b064ybzkwf1y

I also looked in v$sql and could not find any records with this sql_id. Could someone help out with some pointers explaining the reasoning behind this?

Thanks

  • 1
    Are you running RAC? – Philᵀᴹ Sep 29 '16 at 18:30
  • 2
    AFAIK v$session_longops will retain some entries until those slots are re-used. These are not immediately discarded as soon as session is gone, because there is no need to. If you cannot find matching sid in v$session, those are stale entries. If you want to know what those might be, look in ash or awr or v$sql for that sqlid. – Raj Sep 29 '16 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.