CODEWITHSUNDEEP
sqloracle-database
UserControl
UserControl

Reputation: 15159

Drop user cascade in Oracle

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.

Upvotes: 7

Views: 15567

Answers (4)

Stephanie Page
Stephanie Page

Reputation: 3893

It's a very, very bad idea to take a construct from one Database platform and assume I can run the exact same thing on a different platform. For example. Oracle has Create OR REPLACE procedure. MSSS isn't quite so simple. MSSS you can make a "temp" table with #name, in Oracle we use DDL. While dropping a user to recreate a fresh environment may have been the simplest approach on MSSS, perhaps there's a more Oracle-centric way to accomplish the same thing. It's a very good idea to ask for help on how to accomplish a task instead of why your way isn't working.

First, does the app being tested do DDL? to the tables and other objects?

If it only changes data, they way Oracle prefers apps to work, then why do you have to recreate all the objects. You just need to get the data back to the starting point.

Have you looked into Flashback Database? You should be able to create a restore point... do whatever you want and then flashback the database to that point in time.

Upvotes: 0

gsiems
gsiems

Reputation: 9

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'

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40499

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

Upvotes: 3

Harrison
Harrison

Reputation: 9090

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

Upvotes: 0

Related Questions