OracleDatabase: securely deletes database users.

Before using dropusercascade, killsession is usually used to terminate the currently connected session. However, for a long running transaction that has not been committed

Before using drop user cascade, kill session is usually used to terminate the currently connected session. However, for a long running transaction that has not been committed

Kill Session

Before using drop user cascade, kill session is usually used to terminate the currently connected session. However, if a long running transaction has not been committed, it usually takes a long time to kill a session. In addition, it is troublesome to kill the corresponding session process at the operating system level. Otherwise, the following error occurs:

ORA-01940: cannot drop a user that is currently connected

Disconnect Session

If you use disconnect session immediate, it is relatively convenient to terminate the current session and kill the process at the OS level.

The procedure provided below can perfectly delete database users. First, use alter user account lock to lock the user to prevent connections to other new sessions. Loop disconnect each existing session connection, wait until all sessions are deleted, and finally drop.

SET SERVEROUT ON

Create or replace procedure gracefullyDropUser (v_username IN VARCHAR2) IS
Rochelle CNT integer;
SqlStmt VARCHAR2 (1000 );
BEGIN
SqlStmt: = 'alter user' | v_username | 'account lock ';
Execute immediate sqlStmt;
Dbms_output.put_line (sqlStmt );
FOR x IN (SELECT * FROM v $ session WHERE username = v_username)
LOOP
SqlStmt: = 'alter system disconnect session ''' | x. sid | ',' | x. serial # | ''' IMMEDIATE ';
Execute immediate sqlStmt;
Dbms_output.put_line (sqlStmt );
End loop;

-- Wait until all sessions are disconnected forcely, check every 2 seconds
LOOP
Select count (*) INTO l_cnt FROM v $ session WHERE username = v_username;
Exit when l_cnt = 0;
Dbms_lock.sleep (2 );
Dbms_output.put_line ('Hold on ...');
End loop;
SqlStmt: = 'drop user' | v_username | 'cascade ';
Execute immediate sqlStmt;
Dbms_output.put_line (sqlStmt );
END gracefullyDropUser;
/

The following example calls the above process and deletes the user named 'agile. Note that the username is capitalized.

SQL> execute gracefullyDropUser ('agile ');
Alter user AGILE account lock
Alter system disconnect session '12, 97 'IMMEDIATE
Alter system disconnect session '20, 11' IMMEDIATE
Alter system disconnect session '000000' IMMEDIATE
Hold on...
Hold on...
Drop user AGILE cascade

PL/SQL procedure successfully completed.