清空Schema中所有物件的步驟

先轉一段：

如果想要快速的刪除一個Schema下的所有資料庫物件，您會使用什麼樣的手段來完成呢？

如果您是DBA，可能更傾向於先刪除使用者然後再重新建立使用者。
優點：刪除的徹底，不留任何痕跡（這是DBA偉大和危險的真實體現）。
缺點：要求的操作許可權比較高，往往需要DBA親力親為；有一定誤操作的風險；不便於書寫到自動化指令碼中，因為在當前使用者有session連線的情況下是無法實現使用者刪除的。

如果您是一名開發人員，並且是位“工具達人”，可能會傾向於使用開發工具（如Toad、PL/SQL Developer等）點選的方式完成清理工作。
優點：要求許可權不高，如果工具功能掌握熟練可以較快的完成刪除任務。
缺點：無法實現自動化和批次操作的目的，人工成本太高，效率極低；存在工具軟體異常假死的現象，不推薦使用。

 

這裡按DBA，並且被要求不能刪除使用者的情況下，步驟如下：

---- Check out the DB object types in this schema as sysdba

SQL> select owner,object_type,count(1) from dba_objects where owner like 'VERTEX%' group by owner,object_type order by 1,2;

OWNER                          OBJECT_TYPE           COUNT(1)
------------------------------ ------------------- ----------
VERTEX_SALES                   INDEX                      535
VERTEX_SALES                   SEQUENCE               12
VERTEX_SALES                   TABLE                      326
VERTEX_SALES                   TRIGGER                   12
VERTEX_SALES                   VIEW                          7
因為這裡INDEX和TRIGGER都是在TABLE上的，所以只需drop掉 TABLE/VIEW/SEQUENCE即可。

 

---- generate SQL scripts running SELECT DROP clause below on SQLPLUS.

SQL> select 'drop table '||owner||'.'||table_name||' cascade constraints;' from dba_tables  where wner='VERTEX_SALES';

SQL> select 'drop sequence '||sequence_owner||'.'||sequence_name||';'||chr(13)||chr(10) from all_sequences where sequence_owner='VERTEX_SALES';

SQL> select 'drop view '||owner||'.'||view_name||' cascade constraints;'||chr(13)||chr(10) from all_views where wner='VERTEX_SALES';

---- execute the SQL script genetated on the top one by one on SQLPLUS

SQL> @/tmp/salesdt.sql

SQL> @/tmp/salesdv.sql

注意：執行之前需要對sql指令碼修改加工，比如去掉標題和結果等，也可以在生成之前在sqlplus裡set verify off pagesize 0 linesize 150 echo off feedback off head off timing off

然後再執行第一步檢查是否全部清空

----- import the dmp file exported the source env.

 /export/home/oracle > imp "'/ as sysdba'" file=vertex.dmp fromuser=VERTEX_SALES touser=VERTEX_SALES

注意：匯入之前可能需要設定匯入字符集和新增角色等，那就先create role 。。。

---- See if the import has been completed running the first SQL clause.

 

完整的SELECT DROP語句如下：

select 'drop table '||table_name||' cascade constraints;' from all_tables where wner='TEST';
select 'drop view ' || view_name||' cascade constraints;'||chr(13)||chr(10) from all_views  where wner='TEST';
select 'drop index ' || index_name||' cascade constraints;'||chr(13)||chr(10) from all_indexes  where wner='TEST';
select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from all_sequences where sequence_owner='TEST';
select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from all_objects where object_type='PROCEDURE' and wner='TEST';
select 'drop trigger ' || object_name||';'||chr(13)||chr(10) from all_objects where object_type='TRIGGER' and wner='TEST';
select 'drop package ' || object_name||';'||chr(13)||chr(10) from all_objects where object_type='PACKAGE' and wner='TEST';
---- The end.