Thinking in Robert

結論是

在沒有將Constrains 給 Disable 前，不能直接Delete 或將 Drop  TABLE
這樣將會違反了oracle 的 Data 完整性。

以下為Robert 要將一個巨大的 Table ,刪去大部分的資料再重建，順便釋放 TableSpace
( K 國人程式寫不好 , 一天竟然有 13.5 萬筆資料寫入, 而且成長快速 , 目的與實作的結果差很遠)
所以 Robert 來收尾..

DROP TABLE  TEST ;

刪除 TABLE 時遇到以下問題

ORA-02449 unique/primary keys in table referenced by foreign keys


Cause: An attempt was made to drop a table with unique or primary keys referenced by foreign keys in another table.

Action: Before performing the above operations the table, drop the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command:

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

改用

TRUNCATE TABLE TEST ;

卻遇到新的

    ORA-02266 unique/primary keys in table referenced by enabled foreign keys


Cause: An attempt was made to drop or truncate a table with unique or primary keys referenced by foreign keys enabled in another table.

Action: Before dropping or truncating the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command:

select constraint_name, table_name, status
from user_constraints
where r_constraint_name in (
select constraint_name from user_constraints where table_name ='tabnam');

我想應該是有別的 TABLE 上有 FOREIGN KEY 關連到這個 TEST TABLE 的關係

找了一篇文章  http://www.itpub.net/thread-476654-1-1.html

提及可以用這種方式改善 ==> 停掉別的 TABEL 的 FOREIGN KEY

alter table test2 disable constraint F_TEST2;
truncate table test1;
alter table test2 enable constraint F_TEST2;

等待 TRUNCATE TABLE TEST ; 完成 , 再將那個 FK 恢復即可

不需 RECREATE TABLE ...直接把備份的資料匯入即可