安全地刪除數據庫用戶

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'; --循環disconnect每一個已存的session連接，并等待所有session被刪除
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;
/

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.