删除用户的存储过程

create or replace procedure prc_drop_user( iv_user_name in varchar2)

authid current_user

is

    vv_err_info varchar2(300);

    cursor c_session is

      select sid, serial# serial

        from v$session

       where username = upper(iv_user_name);

    c_session_row c_session%rowtype;

begin

 

    execute immediate 'alter user ' || iv_user_name || ' identified by passwdtemp';

    for c_session_row in c_session loop

        execute immediate 'alter system kill session ''' || c_session_row.sid || ',' || c_session_row.serial || '''';

    end loop;

    execute immediate 'drop user ' || iv_user_name || ' cascade';

 

exception

    when others then

        vv_err_info := sqlcode || substr(sqlerrm,1,300);

        DBMS_OUTPUT.PUT_LINE('Drop user '|| iv_user_name ||'failed, errer info is:' || vv_err_info);

end;

/

 

exec prc_drop_user('lbi_sys');