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.
The user will not be dropped and an error message will be returned if you a user owns objects and you fail to use the Oracle DROP USER CASCADE command. As the DROP USER CASCADE command is a DDL command, after the Oracle DROP USER CASCADE command has been executed, a rollback to re-create the user and his objects cannot be completed.
Dropping user in Oracle remove user and it’s contents from the database. You must use CASCADE keyword to remove all objects owned by the schema. Sometimes users are connected to the database and it takes long time to drop. So in this case you can drop forcefully by killing user session connected to the database.
The Oracle DROP USER CASCADE command drops a user and all owned objects. ... As the DROP USER CASCADE command is a DDL command, after the Oracle DROP USER CASCADE command has been executed, a rollback to re-create the user and his objects cannot be completed.
To drop any user, you need to lock related user firstly. SQL> alter USER MSD account lock; User altered. Then kill the session of related user as follows. Kill these sessions as follows. SQL> alter system kill session '163,263'; System altered. SQL> alter system kill session '634,63'; System altered. SQL> drop user MSD cascade; User dropped.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With