We have a C++ application which creates multiple DB Connection sessions against an Oracle schema, say EMPSCHEMA. For developers' needs sometimes i'll have to drop and recreate EMPSCHEMA.
But when i try to drop this schema i get the error message:
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
So i lock the account and try to kill the sessions using the following script
alter user EMPSCHEMA account lock;
set serveroutput ondeclare
sqlStmt VARCHAR2(1000);
BEGIN
For x in (SELECT SID,SERIAL#,TERMINAL FROM V$SESSION WHERE schemaname='EMPSCHEMA') loop
sqlStmt := 'ALTER SYSTEM KILL SESSION ''' ||X.SId ||',' ||X.Serial# ||'''' ;
dbms_output.put_line( sqlStmt);
dbms_output.put_line( x.terminal);
EXECUTE IMMEDIATE sqlStmt;
End loop;
end;
But i can still see sessions spooled by the above mentioned C++ application, somehow getting connected to EMPSCHEMA. This means that the
alter user EMPSCHEMA account lock;
was useless. Is there any way that i can make sure that the Schema is locked and no session can connect to it?
Message was edited by:
Seymour