oracle删除schema及相关的所有数据库的对象

hcwei 2020年08月07日 81次浏览
  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
  1. 给脚本授予可执行权限
chmod +x drop_scheme.sql
  1. 登录oracle,执行脚本
sqlplus /nologin
@drop_schema.sql