如果想要快速的删除一个Schema下的所有数据库对象，您会使用什么样的手段来完成呢？

如果您是DBA，可能更倾向于先删除用户然后再重新创建用户。
优点：删除的彻底，不留任何痕迹（这是DBA伟大和危险的真实体现）。
缺点：要求的操作权限比较高，往往需要DBA亲力亲为；有一定误操作的风险；不便于书写到自动化脚本中，因为在当前用户有session连接的情况下是无法实现用户删除的。

如果您是一名开发人员，并且是位“工具达人”，可能会倾向于使用开发工具（如Toad、PL/SQL Developer等）点选的方式完成清理工作。
优点：要求权限不高，如果工具功能掌握熟练可以较快的完成删除任务。
缺点：无法实现自动化和批量操作的目的，人工成本太高，效率极低；存在工具软件异常假死的现象，不推荐使用。

有没有更快更好并且低风险的完成这个“伟大”任务的方法呢？

个人认为使用PL/SQL定制一个随需而变的脚本是一个比较好的想法。
我在这里把这种方法给大家介绍一下，供朋友们参考。

1.脚本编写思想
使用v$session和v$mystat视图获得当前session所连接的用户名；
使用ALL_*视图得到待删除数据库对象列表；
为防止误操作，限制不允许删除系统用户中的内容；
还可以进一步加入个性化的需求。

2.脚本内容
此脚本功能是清除当前连接用户中的表和序列。这两个数据库对象想必是最常用到的，如果还存在其他数据库对象，可以在此基础上进行修改和添加，直到满足全部需求。
-- This PL/SQL will purge tables in this schema (BTW: Not in SYS schema).

DECLARE
   v_sqlstring   VARCHAR2 (500);
BEGIN
   FOR rec
      IN (SELECT table_name
            FROM all_tables
           WHERE wner = (SELECT t1.username
                            FROM v$session t1, v$mystat t2
                           WHERE t1.sid = t2.sid AND ROWNUM < 2)
             AND owner NOT IN
                       ('SYSTEM',
                        'SYS',
                        'SYSMAN',
                        'DBSNMP',
                        'MGMT_VIEW',
                        'OUTLN',
                        'WMSYS',
                        'DIP',
                        'TSMSYS'))
   LOOP
      BEGIN
         v_sqlstring :=
            'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';

         --dbms_output.put_line(v_sqlstring);
         EXECUTE IMMEDIATE v_sqlstring;
      END;
   END LOOP;
END;
/

-- Check if the tables in this schema have been purged.

SELECT * FROM tab;

-- This PL/SQL will purge sequences in this schema.

DECLARE
   v_sqlstring   VARCHAR2 (500);
BEGIN
   FOR rec
      IN (SELECT sequence_name
            FROM all_sequences
           WHERE SEQUENCE_OWNER = (SELECT t1.username
                                     FROM v$session t1, v$mystat t2
                                    WHERE t1.sid = t2.sid AND ROWNUM < 2)
             AND SEQUENCE_OWNER NOT IN
                       ('SYSTEM',
                        'SYS',
                        'SYSMAN',
                        'DBSNMP',
                        'MGMT_VIEW',
                        'OUTLN',
                        'WMSYS',
                        'DIP',
                        'TSMSYS'))
   LOOP
      BEGIN
         v_sqlstring := 'drop sequence ' || rec.sequence_name;

         --dbms_output.put_line(v_sqlstring);
         EXECUTE IMMEDIATE v_sqlstring;
      END;
   END LOOP;
END;
/

-- Check if the sequences in this schema have been purged.

SELECT * FROM seq;

-- Check if all the database objects in this schema have been purged.

SELECT * FROM obj;

3.脚本使用前提条件
因为脚本中使用到v$session和v$mystat视图来确定当前session所连接的用户名，因此用户需要有查询这两个视图的权限。
为实现任何用户都可以查询这两个视图的目的，可以将查询权限授权授权给PUBLIC（注意这里是使用真正意义上的视图v_$session和v_$mystat实现的授权）。
sys@ora10g> grant select on v_$session to public;

Grant succeeded.

sys@ora10g> grant select on v_$mystat to public;

Grant succeeded.

4.脚本使用方法
最简单的方法：在SQL*Plus命令行界面中拷贝粘贴上面脚本内容，便可实现删除当前所连接用户中数据库对象的目的。
另外一种使用方法：将脚本的内容嵌入到其他脚本之中，以便实现更加复杂的功能需求。

5.重点强调
上面的PL/SQL脚本虽然可以轻松的完成数据库对象的删除任务，但因使用到DDL语句，在使用前一定要做好测试工作，充分认识到操作后果的严重性。强烈建议在使用我这段脚本之前对数据库做好备份，以防不测。
您还可以在我这个脚本的基础上增加更多的限制判断条件，以便降低误操作的可能性。

6.小结
1）PL/SQL可以实现的功能非常丰富，要善加利用；
2）实现自动化需要缜密的规划和甄选，以便选择一个最适合需求的方案；
3）自动化删除具有相当大的风险，使用时一定要头脑清醒，不可懈怠；
4）防止误操作唯一有效的手段就是“备份”！“备份”是惊魂未定之时唯一的救命稻草。

Good luck.

-- The End --

ITPub博客

【SCRIPTS】快速清理Schema中所有表和序列的PL/SQL脚本

最新文章