Check out if SQL Query is really hanged or not

1. Find the active sessions

SQL> 
SQL> select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='&username';
Enter value for username: SYS
old   1: select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='&username'
new   1: select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='SYS'

       SID    SERIAL#
---------- ----------
USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS                   SQL_ID                                  LOGON
------------------------ --------------------------------------- ---------------------------------------------------------------------------------------
EVENT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         2      59121
SYS
ACTIVE                                                           01-FEB-2023 15:16:00
OFS idle

       102      24168
SYS
ACTIVE                   6g5gsu0mcqmc5                           02-FEB-2023 10:17:57
SQL*Net message from client


Elapsed: 00:00:00.00
SQL> SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='&username' and status not like '%DONE%';
Enter value for username: SYS
old   1: SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='&username' and status not like '%DONE%'
new   1: SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='SYS' and status not like '%DONE%'

no rows selected

Elapsed: 00:00:00.02
SQL>

2. Monitoring Progress of a SQL Execution Plan

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%' and sid='&sid' <---
ORDER BY 1,4;

-- OR ---

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;

3. REPORT_SQL_MONITOR in HTML (OR) TEXT format

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_sql_monitor.txt
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '5mxdwvuf9j3vp', <--- SQLID
  type         => 'TEXT',         <--- HTML
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF