Oracle - How to execute this script in parallel

Question

I have a script to kill all sessions in Oracle:

declare
begin
for rec in (
SELECT username,machine,sid,serial#,inst_id from gv$session where type <> 'BACKGROUND' 
and sid <> (select sys_context('userenv','sid') from dual) and status <> 'KILLED'
and username not in ('SYSRAC','SYS')
) 
loop
execute immediate 'alter system disconnect session '''|| rec.sid|| ',' || rec.serial# || ',@' ||rec.inst_id||''' immediate';
end loop;
end;
/

Once a month I need kill all session before execute several scripts to add/remove/modify columns, grants and revokes, create new objects, drop objects etc..

This database usually has more than 2000 connections and when I execute this script it takes from 15 to 20 minutes to kill all sessions. How can I execute this script in parallel and kill all sessions faster?

Connor McDonald · Accepted Answer · 2021-01-25 16:00:22Z

3

A few things to absorb there ...

2000 connections sounds like overkill, especially if you're killing all of them which suggests they all belong to the same app. I'd be checking your connection pooling stats to ensure this isn't too many.
It might be worth checking out edition based redefinition to allow deployment without any downtime
If you're killing everything anyway, just do shutdown abort/startup and you're done
If you really want to kill those sessions, then you can use DBMS_PARALLEL_EXECUTE to get the job scheduler to run these concurrently.

But step back and take a look at the whole picture here, because generally, we don't kill all sessions to deploy code.

answered Jan 25, 2021 at 16:00

Connor McDonald

11.9k1 gold badge15 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Oracle - How to execute this script in parallel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related