[long_delete-update-merge_query_with_qc_info.sql] Display DELETE / UPDATE / MERGE running for more than 30 minutes (with SID / serial of parallel coordinator process) #Oracle

long_delete-update-merge_query_with_qc_info.sql

select sql_exec_start,username,machine,module,program,
(select px.QCSID||','||px.QCSERIAL# ||',@'||px.QCINST_ID from gv$px_session px where  px.sid=vs.sid and  px.serial#=vs.serial#) as QC_info,
(select substr(sql_text,1,80) from gv$sql vsql where vsql.sql_id = vs.sql_id and rownum = 1) as SQLTEXT
from v$session vs
where
vs.sql_exec_start < SYSDATE - 1 / 48
and
EXISTS (
            SELECT
                1
            FROM
                gv$sql vsql
            WHERE
                    vsql.sql_id = vs.sql_id
                AND
                    vsql.command_type IN (
                        6,7,189
                    )
                    )
ORDER BY (select px.QCSID||','||px.QCSERIAL# ||',@'||px.QCINST_ID from gv$px_session px where  px.sid=vs.sid and  px.serial#=vs.serial#)
;