Skip to Main Content
  • Questions

  • Drop an Oracle user (Schema) using JDBC java

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Houari.

Asked: January 10, 2018 - 2:49 pm UTC

Last updated: January 15, 2018 - 9:12 am UTC

Version: 11gr2

Viewed 1000+ times

You Asked

Hi Tom;

I am developing a java application using oracle as database.

I connect to the database once per program execution using :
try {
pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("user");
pds.setPassword("pwd");
pds.setInitialPoolSize(10);
pds.setMinPoolSize(10);
pds.setMaxPoolSize(15);
} catch (SQLException e1) {
e1.printStackTrace();
}

I have a java class to ' reinitialyze the DB ' ==> drop a user and create-it again and its objects using :

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@//127.0.0.1:1521/XE", "sys as sysdba", "pwd");
statement = connection.createStatement();

req = "select 'alter system kill session ''' || sid || ',' || serial# || ''';' from GV$SESSION where username LIKE 'USER'";
statement.executeQuery(req);

req = "DROP USER IPACK CASCADE"; //<== here is the problem
statement.executeUpdate(req);

//create the user again and its objects

} catch (SQLException e1) {
e1.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

BUT I GOT ALWAYS THE ERROR : java.sql.SQLSyntaxErrorException: ORA-01940: cannot drop a user that is currently connected

I use Oracle 11gr2
Java 8, ojdbc6.jar and ucp.jar

So where is the problem ?

Thanks in advance.

and Connor said...

Killing all sessions for a user does NOT mean those sessions disappear immediately. So you will need to pool gv$session after the kill command to see when those sessions are cleaned up.

For example

SQL> conn demo/demo
Connected.
SQL> create table t as
  2  select rownum x, rownum y, rownum z
  3  from dual
  4  connect by level <= 1000000;

Table created.

SQL> create index ix1 on t ( x );

Index created.

SQL> create index ix2 on t ( y );

Index created.

SQL> create index ix3 on t ( z );

Index created.

SQL> create index ix4 on t ( x,y );

Index created.

SQL> create index ix5 on t ( y,z );

Index created.

SQL> create index ix6 on t ( z,x );

Index created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> delete from t;

1000000 rows deleted.


So I've deliberately loaded up a large workload of uncommitted changes here. Now I'm going to kill that session, which means all that work will need to be rolled back, which will take some time.

SQL> conn / as sysdba
Connected.
SQL> alter system kill session '258,64049';

System altered.

SQL> select sid, status, username from v$session where username = 'DEMO';

       SID STATUS   USERNAME
---------- -------- ---------------------------------------------
       258 KILLED   DEMO


You can see the session is killed, but still there, and hence:

SQL> drop user demo cascade;
drop user demo cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected


I wait a little longer, and eventually the session disappears and then:

SQL> drop user demo cascade;

User dropped.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Drop Oracle user using jdbc

Houari, January 11, 2018 - 10:53 am UTC

Hi tom;
I try to translate your answer by testing when sessions are killed using :

boolean b = true;
while(b) {
req = "SELECT 1 FROM V$SESSION WHERE USERNAME = 'USER' AND STATUS NOT LIKE 'KILLED'";
resultset = statement.executeQuery(req);
if(resultset.next()) {
System.out.println("NOT YET");
try {
Thread.sleep(30000);
} catch (InterruptedException e1) {
e1.printStackTrace();
}
}else {
b = false;
break;
}
}

but its been long time and nothing happened( I am stuck in "NOT YET").

But when i use SQL developper to do this :

alter system kill session '<sid>,<serial#>';
DROP USER USER CASCADE;

it works fine and user dropped in less than 30 seconds.
the problem is my app users shouldn't use SQL developper, but a java GUI !

so is there a better way to drop this oracle user using jdbc whitout waiting lot of time.

thanks in advance.
Connor McDonald
January 12, 2018 - 12:11 am UTC

KILLED or NOT, you do not want a session, so I'd look at your query being:

SELECT 1 FROM V$SESSION WHERE USERNAME = 'USER'

Now, it is possible your 'alter' command is not being executed correctly. You might want to replace it with an anonymous block

begin execute immediate 'alter system kill ....' end;

Drop Oracle user (schema) using JDBC java

Houari, January 13, 2018 - 9:10 am UTC

Hi Tom;

i follow your advice using :

CallableStatement cs = connection.prepareCall("BEGIN " +
" FOR r IN (select sid,serial# from v$session where username='IPACK') " +
" LOOP " +
" EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# || ''' immediate'; " +
" END LOOP; " +
" END;");
cs.executeUpdate();

boolean b = true;
while(b) {
req = "SELECT 1 FROM V$SESSION WHERE USERNAME = 'IPACK'";
resultset = statement.executeQuery(req);
if(resultset.next()) {
System.out.println("NOT YET");
try { Thread.sleep(20000); } catch (InterruptedException e1) { e1.printStackTrace(); }
}else {
break;
}
}

req = "DROP USER IPACK CASCADE";
statement.executeUpdate(req);

and every thing work fine.
I think that the key-word was 'immediate'.

You help me a lot.
Thank you so much and keep doing this.
Connor McDonald
January 15, 2018 - 9:12 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database