oracle drop columns

從oracle 8i開始能夠刪除表中的列。以前則需要刪除整個表然後重建的方式來刪除表中的列。現在有兩種刪除方式，一種是將要刪除的列設定為unused，從而邏輯上刪除。另外一種方式是透過alter table ...drop column 的方式物理上刪除。

邏輯上刪除

當在一張很大的表中物理上刪除列的時候，需要消耗很多的資源和時間，這時候我們可以簡單的把要刪除的列設定為unused，這就是從邏輯上面刪除了表中的列（對資料庫中後設資料進行的修改）。

SQL> create table emp as select * from employees;

Table created.--建立一張測試用的表，結構和employees一致。

SQL> alter table emp set unused (salary);

Table altered.--將salary 列設定為unused.

這時候使用desc 檢視錶的結構就不會存在salary 這一列了，該列對使用者是不可見不可用的。

SQL> select max(salary) from emp;

select max(salary) from emp

*

ERROR at line 1:

ORA-00904: "SALARY": invalid identifier

可以透過*_unused_col_tabs 檢視到表中刪除的列的數量。

SQL> select * from dba_unused_col_tabs

2 where table_name = 'EMP';

OWNER TABLE_NAME COUNT

------------------------------ ------------------------------ ----------

HR EMP 1

如果需要回收設定為unused 的列所佔用的空間，可以使用以下的語句。

checkpoint 的意義在於當在一個很大的表中刪除一個列的時候需要長時間佔用很多的undo 空間,這可能會影響到其他操作對undo 空間的獲取，所以指定處理完一定數量的列以後，執行一次checkpoint 釋放undo空間。

SQL> alter table emp drop unused columns checkpoint 250;

Table altered.

上述語句會把某張表中所有設定為unused 的列都刪除掉。同時需要注意的是設定為unused 的列是不可以恢復的。不存在類似rollback 的方式。

物理上刪除

可以使用以下的方式物理上面刪除一個列：

SQL> alter table emp drop column commission_pct;

Table altered.--一次只刪除一個列。

SQL> alter table emp drop (first_name,last_name);

Table altered.--一次刪除多個列。

物理上刪除的列也不存在類似rollback 的恢復方式。

注：不能刪除一個表中所有的列。

當物理上刪除一個表中的列的時候，該表中所有設定為unused 的列也會被刪除。

oracle drop columns

相關文章