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:
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:
sqlplus sys/*** as SYSDBA @delete1.sql >delete.log
sqlplus sys/***@XE as SYSDBA @delete2.sql >>delete.log
where delete1.sql:
startup force;
exit;
and delete2.sql:
drop user MYUSER cascade;
exit;
This is ugly as hell and takes too long comparing to the split second of MSSQL solution.
It should work if you use the following script (here named drop_user_with_active_sessions.sql
):
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
sqlplus username/password@instance @c:\path\to\drop_user_with_active_session.sql MYUSER