CODEWITHSUNDEEP
oracle-databaseplsql
Kalyan
Kalyan

Reputation: 378

What is the way to kill an executing procedure?

I am using Oracle sql developer. I have a procedure involving MINUS statement between two tables (table 1 and table 2). The table 1 has more than a million rows data. In this situation, when I am trying to execute it, it returns nothing but is in executing stage.

Meanwhile, I need to truncate this table data. But, when doing this, I got a resource busy error. How to terminate the executing stored procedure and how to do truncate operation meanwhile?

Upvotes: 5

Views: 5790

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You do not kill a procedure, you kill a session.

Before killing the session, you might want to know that readers do not block writers and vice-versa. So, if you want to select/project the rows from the table being modified, you could always do it.

  1. Identify the session:
SELECT s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   v$session s
       JOIN v$process p ON p.addr = s.paddr
WHERE  s.type != 'BACKGROUND';
  1. Kill the session:
ALTER SYSTEM KILL SESSION 'sid,serial#';

The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.

In addition to the syntax described above, you can add the IMMEDIATE clause.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

Read this article for more details.

Upvotes: 7

Related Questions