Forum Stats

  • 3,825,281 Users
  • 2,260,497 Discussions
  • 7,896,476 Comments

Discussions

How to stop the below query from execution DELETE FROM SNP_SESS_TASK_LOG WHERE SESS_NO IN ( SELECT S

This is creating undo tablespace issue and I am not sure from where we can stop the same. Any help would be appreciated.

Tagged:

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,974 Blue Diamond
    edited Apr 3, 2022 11:58AM


    I can think of a very dirty method to stop it running - assuming the statement never changes and you have the SQL_ID - but it would be better to come up with a legal strategy for dealing with the problem.

    You haven't told us anything about the undo tablespace issue (tablespace full, too much I/O from other sessions on the undo tablespace, this session crashing and rolling back, other sessions failing and rolling back, tablespace growing to ridiculously huge size, other) but we might guess that the delete is generating a lot of undo and that's producing an undesirable side effect.

    If that guess is correct you might investigate what the statement does and do it by hand in several stages. For example if it has a clause like "where date_created < sysdate - 30" but this turns out to be 5 years worth of data, you might run a series of statement that start 5 years back and delete one months worth of data at a time so that the next time this statement runs there's only a little data left for it to delete. (And at that point you might try moving this table "online" so that the vast amount of empty space is released to the tablespace and any indexes are tidied up).

    Regards

    Jonathan Lewis


    If you can see v$session when this statement is running then various columns in the view may give you some ideas about where it's running from - e.g. module, application, client_info, client_identifier, plsql_object_id, username, program.

    If you can't get anything from the dynamic views you might find something in dba_source if you searched for lower(text) like '%snp_sess_task_log%'


    And if you are after a way of killing it intermittently rather than working out how to deal with it then maybe Tanel's notes will help: https://tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/ (the alter system cancal SQL bit, in particular)