Dropping a connected user from an Oracle 11g database schema

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

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

 ------

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';

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

-----------

 DECLARE
 lc_username VARCHAR2 (32) := 'your user name 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; /

--------------

Disconnect and drop user

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user <USERNAME> account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = '<USERNAME>')
  LOOP
    EXECUTE IMMEDIATE 'alter system disconnect session ''' || x.sid || ',' || x.serial# || ''' IMMEDIATE';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = '<USERNAME>';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 2 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user <USERNAME> cascade';
  EXECUTE IMMEDIATE 'CREATE USER <USERNAME> IDENTIFIED BY <USERNAME> DEFAULT TABLESPACE WCC_OCS TEMPORARY TABLESPACE WCC_OCS_TEMP ACCOUNT UNLOCK';  
  EXECUTE IMMEDIATE 'ALTER USER <USERNAME> QUOTA UNLIMITED ON WCC_OCS';
  EXECUTE IMMEDIATE 'GRANT "DBA", "CONNECT" TO <USERNAME>';
  EXECUTE IMMEDIATE 'ALTER USER <USERNAME> DEFAULT ROLE "DBA","CONNECT"'; 
  EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.DBMS_LOCK TO <USERNAME>';
 
END;