Sometime SQL queries takes long time to complete. You can check long running sessions using v$session_longops script which will show you, % completed, remaining time, sofar completed and much more detailed information. You can also use below queries to kill long running sessions in Oracle.
Monitor long running sessions in Oracle using below queries:
QUERY 1:
SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
QUERY 2:
set lines 300
col TARGET for a40
col SQL_ID for a20
select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING;
From the above output you can further check the sql_id, sql_text and the wait event for which query is waiting for.
TO find out sql_id for the above sid:
SQL> select sql_id from v$session where sid='&SID';
To find sql text for the above sql_id:
SQL> select sql_fulltext from V$sql where sql_id='bgf07y9xn8grx';
To find wait event of the query for which it is waiting for:
SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';
To kill session in Oracle:
https://orahow.com/4-best-ways-to-find-blocking-sessions-in-oracle-11g/