Cascade of user drops in Oracle

Question

I need to remove a specific user (who may have active sessions) from the package without any user interaction. I do not care about active sessions and want them to be dropped and rolled back. For Microsoft SQL, I would perform a similar task with one 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 to do this for Oracle (10g XE on Windows)?

My current batch:

 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, damn it, and takes too much time compared to the second second of the MSSQL solution.

Answer 1

It should work if you use the following script (here called 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 use it with

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

Answer 2

you can do Oracle SQL through the command line and then do cascade drop .

I would recommend creating an sql script and executing it from the command line .

then you can complete the command line text in your cmd / batch file.

but if you want Oracle to handle the whole process, I would recommend exploring the work / schedule environment

Answer 3

In addition to the "alter system kill session" mentioned above, I also needed a preface to the kill session with something like:

 execute immediate 'ALTER SYSTEM DISCONNECT SESSION ''' || to_char(s.sid) || ', ' || to_char(s.serial#) || ''' IMMEDIATE' 

Answer 4

It is a very, very bad idea to take a design from one database platform and assume that I can run the same thing on another platform. For example. Oracle has a Create OR REPLACE procedure. MSSS is not so simple. MSSS, you can create a table "temp" C # name, in Oracle we use DDL. Although disabling the user to recreate the new environment may have been the easiest approach to MSSS, there may be a more Oracle-oriented way to do the same. It is a very good idea to ask for help on how to complete the task, and not why your method does not work.

First, does the application verify DDL? to tables and other objects?

If he only modifies the data, they prefer the applications to work, and why you should recreate all the objects. You just need to return the data to the starting point.

Have you looked into the Flashback database? You should be able to create a recovery point ... do whatever you want, and then instantly restore the database to this point.