Shulou(Shulou.com)06/01 Report--

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';-- Lock the user to prevent connections to other new sessions

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';-loops through every existing session connection and waits for all session to be deleted

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';-- safely delete database users

EXECUTE IMMEDIATE sqlStmt

Dbms_output.put_line (sqlStmt)

END gracefullyDropUser

/

Execute gracefullyDropUser ('AGILE')

Alter user AGILE account lock

Alter system disconnect session '12pr 97' IMMEDIATE

Alter system disconnect session '20pm 11' IMMEDIATE

Alter system disconnect session '141118' IMMEDIATE

Hold on...

Hold on...

Drop user AGILE cascade

PL/SQL procedure successfully completed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.