oracle 强制删除指定schema及相关的所有数据库的对象



1）、创建一个drop_schema.sql文件，内容如下

set define on
set serveroutput on
connect / as sysdba

define schema = &1
-- Kill Session connecting to the schemas to be dropped
Declare
    Cursor c_session Is
    SELECT s.sid, s.serial#
      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 = '&schema');
Begin
    For r_session In c_session Loop
        Execute Immediate 'ALTER SYSTEM KILL SESSION '''||r_session.sid||','||r_session.serial#||'''';
    End Loop;
End;
/
-- Drop schemas
Drop User &schema Cascade;

set define off
exit

2） 授权

chmod +x drop_schema.sql

3）执行

$sqlplus /nolog
@drop_schema.sql