Oracle Database: securely deleting Database users

Oracle Database: before you use drop user cascade to safely delete a Database user Kill Session www.2cto.com, you usually use kill session to terminate the currently connected Session, however, it takes a long time to kill a session if a long running transaction has not been committed. 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 directly 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. Www.2cto.com set serverout on create or replace procedure gracefullyDropUser (v_username IN VARCHAR2) IS l_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 IM MEDIATE 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 '141,118 'immediate hold on... hold on... drop user AGILE cascade PL/SQL procedure successfully completed.