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_scheme.sql

3. 登录oracle，执行脚本

sqlplus /nologin
@drop_schema.sql