我們在平時的應用開發及資料庫環境準備的過程中，經常會遇到清理當前使用者下資料庫物件的需求，以便滿足資料庫環境準備。
本文給出一種使用PL/SQL實現清理當前使用者下資料庫物件的方法。

【提別提醒】：由於指令碼中涉及到DROP這種危險的DDL命令，千萬不要在生產環境及系統使用者（SYSTEM及SYS使用者等）下進行嘗試！

1.環境準備
這裡模擬建立表、檢視以及同名。
sec@ora11g> create table t1 (x int);

Table created.

sec@ora11g> create table t2 (x int);

Table created.

sec@ora11g> create view v_t1 as select * from t1;

View created.

sec@ora11g> create view v_t2 as select * from t2;

View created.

sec@ora11g> create synonym syn_t1 for t1;

Synonym created.

sec@ora11g> create synonym syn_t2 for t2;

Synonym created.

sec@ora11g> select OBJECT_NAME,OBJECT_TYPE from obj order by CREATED;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
T1                             TABLE
T2                             TABLE
V_T1                           VIEW
V_T2                           VIEW
SYN_T1                         SYNONYM
SYN_T2                         SYNONYM

6 rows selected.

OK，具有三種資料庫物件的sec使用者準備完畢，此處為每一種資料庫物件建立了多個。

2.準備清理當前使用者資料庫物件的PL/SQL指令碼
由於指令碼結構和內容均比較簡單，不進行註釋。具體指令碼內容如下。
DECLARE

CURSOR l_tables IS
select table_name from user_tables;
CURSOR l_views IS
select view_name from user_views;
CURSOR l_synonyms IS
select synonym_name from user_synonyms;

BEGIN

-- drop tables
FOR rec_tables IN l_tables LOOP
    dbms_output.put_line('Dropping table ' || rec_tables.table_name );

    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE ' || rec_tables.table_name;
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to drop table ' || rec_tables.table_name);
    END;

END LOOP;

-- drop views
FOR rec_views IN l_views LOOP
    dbms_output.put_line('Dropping view ' || rec_views.view_name);

    BEGIN
      EXECUTE IMMEDIATE 'DROP VIEW ' || rec_views.view_name;
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to drop view ' || rec_views.view_name);
    END;

END LOOP;

-- drop synonyms
FOR rec_synonyms IN l_synonyms LOOP
    dbms_output.put_line('Dropping synonym ' || rec_synonyms.synonym_name);

    BEGIN
      EXECUTE IMMEDIATE 'DROP SYNONYM ' || rec_synonyms.synonym_name;
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to drop synonym ' || rec_synonyms.synonym_name);
    END;

END LOOP;

END;
/

3.執行效果驗證
1）執行指令碼過程中的輸出資訊
Dropping table T1
Dropping table T2
Dropping view V_T1
Dropping view V_T2
Dropping synonym SYN_T1
Dropping synonym SYN_T2

PL/SQL procedure successfully completed.

如果在刪除的過程中出現問題，這裡也會給出簡單的報錯提示。

2）測試指令碼執行效果
sec@ora11g> select OBJECT_NAME,OBJECT_TYPE from obj order by CREATED;

no rows selected

可見，當前sec使用者下的表、檢視以及同名清理完畢。

4.小結
這裡給出的僅僅是刪除當前使用者下表、檢視以及同名的PL/SQL指令碼。根據具體的環境，可以按照指令碼的結構自行定製。
滿足實際需求的指令碼才是有價值的，毫無目的性的指令碼編寫皆為虛無！

Good luck.

secooler
11.03.20

-- The End --

【Script】使用PL/SQL快速清理當前使用者下所有資料庫物件

相關文章