How to find queries running on a table in oracle

Question

Hi,

I ran a SQL Delete from a SQL Developer session and the delete was running for a long time since there were large number of records. Due to some Not Responding problem with SQL Developer client, i lost my session, as the SQL Developer window got closed.

I do not have DBA access rights, i can not query views like v$session, v$sqlarea etc. But the SQL is still running on database. As soon as i insert some records, it deletes them. How can i find / confirm that this query is still running before asking the DBA to kill this query.

Answer 1

You should go to your DBA, explain what you think is happening and together analyse the problem.

This part of your question puzzles me:

As soon as i insert some records, it deletes them.

The delete transaction you started can not delete records that you insert after you started the delete transaction, this is called read consistency.

Answer 2

"But the SQL is still running on database. As soon as i insert some records, it deletes them."

It is a bit hard to believe this. If your "SQL Delete" was a straightforward DELETE FROM your_table then it cannot be deleting your new records. Oracle's read consistency model won't allow it. So, either you are mistaken or you did something a lot more complicated.

If you have issued a long running DELETE it may show up as long operation in the view V$SESSION_LONGOPS. Not everything is included. Find out more.

select sid
        , serial#
        , opname
        , sofar
        , totalwork
        , start_time
        , last_update_time
        , time_remaining
        , elapsed_seconds
        , sql_address
        , sql_hash_value
        , sql_id    
from v$session_longops
where username = &your_username
and time_remaining != 0 ;

Without DBA access you won't be able to use the information such as SQL_ADDRESS to look up the precise SQL being run in V$SQL. But hopefully you won't have too many hits.