0

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.

Improve this question

2 Answers 2

Reset to default
3

"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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.