Oracle DB: Show long running operations from GV$Session_LongOps including the name of the accessed partitions
Sometimes it is interesting to know which partitions are currently accessed by long running scans. One possible way to get this info is to use the GV$Session.Row_Wait_Obj# to determine the object that is currently accessed by the DB session. This SQL will select the currently active long ops along with the partition info of the accessed object. The column Object_Belongs_To_Target_Table checks if the partition info gotten by GV$Session.Row_Wait_Obj# is really valid. SELECT l.*, l.Serial# Serial_No, o.Object_Type, o.Owner, o.Object_Name, o.SubObject_Name, CASE WHEN o.Owner = l.Target_Owner AND o.Object_Name = l.Target_Table_Name THEN 'YES' /* Table is accessed */ ELSE CASE WHEN (o.Owner, o.Object_Name) IN (SELECT i.Owner, i.Index_Name FROM DBA_Indexes i WHERE i.Table_Owner = l.Target_Owner AND i.Table_Name = l.Target_Table_Name ...