Oracle Database: 安全地删除数据库用户

来源：互联网发布：想成为网络写手编辑：程序博客网时间：2024/06/05 03:09

Kill Session

在使用drop user cascade之前，通常使用kill session来终止当前已经连接的会话，但对于一个运行中的比较长的事务尚未提交完成的情况下，kill session常需要很长的时间。而且还必须回到操作系统级别kill掉相应的会话进程，比较麻烦。否则会出现下述错误：

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

Disconnect Session

如果使用disconnect session immediate，就直接相对非常方便地终止当前某个会话，并在OS级别kill掉了进程。
下面提供的procedure可以很完美地删除数据库用户。首先使用 alter user account lock锁住用户，防止其他新会话的连接。接着循环disconnect每一个已存的session连接，并等待所有session被删除，最后drop掉。

SET SERVEROUT ONCREATE 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 IMMEDIATE sqlStmt;  dbms_output.put_line(sqlStmt);END gracefullyDropUser;/

下面例子调用上述过程，删除了名为'AGILE'的用户。注意用户名的大写。

SQL> execute gracefullyDropUser('AGILE');alter user AGILE account lockalter system disconnect session '12,97' IMMEDIATEalter system disconnect session '20,11' IMMEDIATEalter system disconnect session '141,118' IMMEDIATEhold on ...hold on ...drop user AGILE cascadePL/SQL procedure successfully completed.