CODEWITHSUNDEEP
oracle-database
Joshua Starner
Joshua Starner

Reputation: 1593

Dropping a connected user from an Oracle 10g database schema

Is there a better way to forcefully disconnect all users from an Oracle 10g database schema than restarting the Oracle database services?

We have several developers using SQL Developer connecting to the same schema on a single Oracle 10g server. The problem is that when we want to drop the schema to rebuild it, inevitably someone is still connected and we cannot drop the database schema or the user while someone is still connected.

By the same token, we do not want to drop all connections to other schemas because other people may still be connected and testing with those schemas.

Anyone know of a quick way to resolve this?

Upvotes: 71

Views: 161215

Answers (6)

RLapinski
RLapinski

Reputation: 195

my proposal is this simple anonymous block:

DECLARE
   lc_username   VARCHAR2 (32) := 'user-name-to-kill-here';
BEGIN
   FOR ln_cur IN (SELECT sid, serial# FROM v$session WHERE username = lc_username)
   LOOP
      EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || ln_cur.sid || ',' || ln_cur.serial# || ''' IMMEDIATE');
   END LOOP;
END;
/

Upvotes: 9

Chand Priyankara
Chand Priyankara

Reputation: 6784

Find existing sessions to DB using this query:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

you'll see something like below. Oracle Sessions

Then, run below query with values extracted from above results.

ALTER SYSTEM KILL SESSION '<put above s.sid here>,<put above s.serial# here>';

Ex: ALTER SYSTEM KILL SESSION '93,943';

Upvotes: 19

LinuxQuestions.org
LinuxQuestions.org

Reputation: 17

just use SQL :

disconnect; 

conn tiger/scott as sysdba;

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48121

Have you tried ALTER SYSTEM KILL SESSION? Get the SID and SERIAL# from V$SESSION for each session in the given schema, then do

ALTER SCHEMA KILL SESSION sid,serial#;

Upvotes: 0

tc
tc

Reputation:

Make sure that you alter the system and enable restricted session before you kill them or they will quickly log back into the database before you get your work completed.

Upvotes: 1

Sten Vesterli
Sten Vesterli

Reputation: 3043

To find the sessions, as a DBA use

select sid,serial# from v$session where username = '<your_schema>'

If you want to be sure only to get the sessions that use SQL Developer, you can add and program = 'SQL Developer'. If you only want to kill sessions belonging to a specific developer, you can add a restriction on os_user

Then kill them with

alter system kill session '<sid>,<serial#>'

(e.g. alter system kill session '39,1232')

A query that produces ready-built kill-statements could be

select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'

This will return one kill statement per session for that user - something like:

alter system kill session '375,64855';

alter system kill session '346,53146';

Upvotes: 121

Related Questions