清除使用者下所有的資料(清庫)

壹頁書發表於2013-11-11
我們的專案出於測試、演示或者研發自己覺得清爽的原因，經常需要清除資料庫中的資料。
但是表間定義了大量的外來鍵關聯，刪除資料還需要遵從一定的業務關係，非常麻煩。
我們的小夥伴通常都是Drop user xxx cascade;然後找到初始化指令碼重新來過。
其實可以在刪除表資料之前，先禁用外來鍵約束，刪除之後，再啟動約束。

首先，建立僱員表和部門表，並且建立一個外來鍵約束。
SQL> create table emp as select * from hr.employees;

表已建立。

SQL> create table dept as select * from hr.departments;

表已建立。

SQL> alter table emp add constraints pk_emp primary key (employee_id);

表已更改。

SQL> alter table dept add constraints pk_dept primary key (department_id);

表已更改。

SQL> alter table emp add constraints fk_dept foreign key (department_id) references dept(department_id);

表已更改。

部門表不能先於僱員刪除，否則報錯。
SQL> delete from dept;
delete from dept
*
第 1 行出現錯誤:
ORA-02292: 違反完整約束條件 (LIHUILIN.FK_DEPT) - 已找到子記錄

SQL> truncate table dept;
truncate table dept
               *
第 1 行出現錯誤:
ORA-02266: 表中的唯一/主鍵被啟用的外來鍵引用

建立禁用約束的命令

  1. SQL> select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;

  3. \'ALTERTABLE\'||TABLE_NAME||\'DISABLECONSTRAINT\'||CONSTRAINT_NAME||\';\'
  4. --------------------------------------------------------------------------------
  5. alter table EMP disable constraint SYS_C0011146;
  6. alter table EMP disable constraint SYS_C0011147;
  7. alter table EMP disable constraint SYS_C0011148;
  8. alter table EMP disable constraint SYS_C0011149;
  9. alter table DEPT disable constraint SYS_C0011150;
  10. alter table EMP disable constraint FK_DEPT;
  11. alter table EMP disable constraint PK_EMP;
  12. alter table DEPT disable constraint PK_DEPT;

  14. 已選擇8行。
執行命令禁用約束，然後建立刪除資料的命令。

  1. SQL> select 'truncate table '||table_name||';' from user_tables;

  3. \'TRUNCATETABLE\'||TABLE_NAME||\';\'
  4. ----------------------------------------------
  5. truncate table DEPT;
  6. truncate table EMP;
執行Truncate命令，最後啟用約束

  1. SQL> truncate table DEPT;

  3. 表被截斷。

  5. SQL> truncate table EMP;

  7. 表被截斷。


  10. SQL> select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints;

  12. \'ALTERTABLE\'||TABLE_NAME||\'ENABLECONSTRAINT\'||CONSTRAINT_NAME||\';\'
  13. --------------------------------------------------------------------------------
  14. alter table EMP enable constraint SYS_C0011146;
  15. alter table EMP enable constraint SYS_C0011147;
  16. alter table EMP enable constraint SYS_C0011148;
  17. alter table EMP enable constraint SYS_C0011149;
  18. alter table DEPT enable constraint SYS_C0011150;
  19. alter table EMP enable constraint PK_EMP;
  20. alter table DEPT enable constraint PK_DEPT;
  21. alter table EMP enable constraint FK_DEPT;

  23. 已選擇8行。

關於約束的命令
ALTER TABLE table_name ENABLE/DISABLE/DROP CONSTRAINT constraint_name;
關於索引的命令
ALTER INDEX index_name VISABLE/INVISABLE/REBUILD/REBUILD ONLINE/UNUSABLE;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-776260/，如需轉載，請註明出處，否則將追究法律責任。

相關文章