drop user cascade in Oracle
It should work if you use the following script (here named drop_user_with_active_sessions.sql):
set verify offbegin 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;/exitAnd the use it with
sqlplus username/password@instance @c:\path\to\drop_user_with_active_session.sql MYUSER
you can do Oracle SQL via the command prompt and then do your cascade drop user.
I would recommend creating a sql script and executing it from the command line.
then you can wrap up command line text in your cmd/batch file.
but if you would like Oracle to handle the entire process I would recommend looking into the job/schedule environment
In addition to "alter system kill session" mentioned above I've also needed to preface the kill session with something like:
execute immediate 'ALTER SYSTEM DISCONNECT SESSION ''' || to_char(s.sid) || ', ' || to_char(s.serial#) || ''' IMMEDIATE'