Dropping a connected user from an Oracle 10g database schema

Question

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

User · Answer

just use SQL    disconnect    conn tiger scott as sysdba

User · Answer

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

User · Answer

To find the sessions  as a DBA use  select sid serial  from v session where username     lt your schema gt    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   lt sid gt   lt serial  gt    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     lt your schema gt    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

User · Answer

To find the sessions  as a DBA use  select sid serial  from v session where username     lt your schema gt    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   lt sid gt   lt serial  gt    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     lt your schema gt    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

User · Answer

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

User · Answer

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

User · Answer

To find the sessions  as a DBA use  select sid serial  from v session where username     lt your schema gt    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   lt sid gt   lt serial  gt    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     lt your schema gt    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

User · Answer

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

User · Answer

Just my two cents   the best way  but probably not the quickest in the short term  would probably be for each developer to work on his own database instance  see rule  1 for database work    Installing Oracle on a developer station has become a no brainer since Oracle Database 10g Express Edition

User · Answer

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    Then  run below query with values extracted from above results   ALTER SYSTEM KILL SESSION   lt put above s sid here gt   lt put above s serial  here gt      Ex  ALTER SYSTEM KILL SESSION  93 943

User · Answer

Just my two cents   the best way  but probably not the quickest in the short term  would probably be for each developer to work on his own database instance  see rule  1 for database work    Installing Oracle on a developer station has become a no brainer since Oracle Database 10g Express Edition

User · Answer

To find the sessions  as a DBA use  select sid serial  from v session where username     lt your schema gt    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   lt sid gt   lt serial  gt    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     lt your schema gt    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

User · Answer

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

User · Answer

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    Then  run below query with values extracted from above results   ALTER SYSTEM KILL SESSION   lt put above s sid here gt   lt put above s serial  here gt      Ex  ALTER SYSTEM KILL SESSION  93 943

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

just use SQL    disconnect    conn tiger scott as sysdba

[oracle] Dropping a connected user from an Oracle 10g database schema

Examples related to oracle