Oracle Check statements currently running on a table


I need to check if a table is being used in a select statement or not before running the command to drop the older partitions using the table.

Below is the query used to check for the same –

SELECT s.sid, s.serial#, s.username, s.program, s.machine
FROM gv$session s
WHERE EXISTS (
SELECT 1
FROM gv$open_cursor c
WHERE c.sid = s.sid
AND c.status = 'OPEN'
AND c.sql_text LIKE '%YOUR_TABLE_NAME%'
);

Leave a comment