drop user cascade in Oracle

Question

I need to be able to drop a specific user (which may have active sessions) from the batch without any user interaction. I don't care about active sessions and want them to be dropped and rolled back. For Microsoft SQL i would do similar task with a single line:

Click to copy

osql -E -S localhost -b -Q "use master if ((select name from sysdatabases where name='%DB%') is not null) begin alter database [%DB%] set single_user with rollback immediate drop database [%DB%] end"

How do i do it for Oracle (10g XE on Windows)?

My current batch is:

Click to copy

sqlplus sys/*** as SYSDBA  @delete1.sql >delete.log
sqlplus sys/***@XE as SYSDBA  @delete2.sql >>delete.log

where delete1.sql:

Click to copy

startup force;
exit;

and delete2.sql:

Click to copy

drop user MYUSER cascade;
exit;

This is ugly as hell and takes too long comparing to the split second of MSSQL solution.

Answer 1

It should work if you use the following script (here named drop_user_with_active_sessions.sql):

Click to copy

set verify off

begin

  for s in (
    select 
      sid, serial#
    from
      v$session
    where 
      username = '&1'
  ) loop

    execute immediate 
       'alter system kill session ''' || 
        s.sid     || ',' ||
        s.serial# || ''' immediate';

  end loop;

  execute immediate 'drop user &1';

end;
/

exit

And the use it with

Click to copy

sqlplus username/password@instance @c:\path\to\drop_user_with_active_session.sql MYUSER