如果想要快速的刪除一個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 --

【SCRIPTS】快速清理Schema中所有表和序列的PL/SQL指令碼

相關文章