Oracle管理监控之测试环境清理用户脚本-优快云博客

本文提供了一种使用PL/SQL删除Oracle数据库中用户的详细步骤。通过声明游标获取目标用户的会话信息，并终止这些会话，最后删除指定用户及其所有对象。文中还介绍了一个存储过程，用于简化此操作。

--PL/SQL块删除用户

declare
cursor cur_duser is
    select sid, serial# from v$session where username in ('T1');
v_str string(200);
begin
for d_u in cur_duser loop
    v_str := 'alter system kill session ' || '''' || d_u.sid || ',' ||
             d_u.serial# || '''';
    execute immediate v_str;
end loop;
v_str := 'drop user T1 cascade';
execute immediate v_str;
end;

declare
cur_duser sys_refcursor;
v_sid     v$session.sid%type;
v_serial# v$session.serial#%type;
v_str     string(200);
uname     string(20);
begin
uname := 't1';
open cur_duser for
    select sid, serial# from v$session where username in (uname);
loop
    fetch cur_duser
      into v_sid, v_serial#;
    exit when cur_duser%notfound;
    v_str := 'alter system kill session ' || '''' || v_sid || ',' ||
             v_serial# || '''';
    execute immediate v_str;
end loop;
close cur_duser;
v_str := 'drop user ' || uname || ' cascade';
execute immediate v_str;
end;

--过程删除用户

create or replace procedure drop_user(uname string) is
cursor cur_duser is
    select sid, serial# from v$session where username in (uname);
v_str string(200);
begin
for d_user in cur_duser loop
    v_str := 'alter system kill session ' || '''' || d_user.sid || ',' ||
             d_user.serial# || '''';
    execute immediate v_str;
end loop;
dbms_lock.sleep(10);
v_str := 'drop user ' || uname || ' cascade';
execute immediate v_str;
end;

转载于:https://www.cnblogs.com/wcwen1990/p/6660446.html