删除用户

--批量删除

declare 
    v_session_sid    varchar2(100);
    v_session_serial varchar2(100);
    type ref_cursor_type is ref cursor;
    cursor_session  ref_cursor_type;
begin
    open cursor_session for 'select sid,serial# from v$session where status <> ''KILLED'' and lower(username) in (''lbi_sys_mk''') ';
    loop    
      fetch  cursor_session into v_session_sid,v_session_serial; 
      exit when cursor_session%notFound;   
      execute immediate 'alter system kill session '''||v_session_sid||','||v_session_serial||'''';    
    end Loop;      
    close cursor_session;
    execute immediate 'drop user lbi_sys_mk cascade';  
   end;

 

 ---单个删

SELECT s.inst_id,
         s.sid,
         s.serial#,
         p.spid,
         s.username,
         s.program,
         s.paddr,
         s.STATUS
  FROM   gv$session s
         JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
  WHERE  s.type != 'BACKGROUND' and s.username='LBI_ODS_MK';

  ALTER SYSTEM KILL SESSION '217,39645' immediate;
 
  drop user lbi_ods_mk cascade;