删除大表中的字段

Dropping Columns

删除大表中的字段
Only with the release of Oracle 8i has it been possible to drop a column from a table. 
Prior to this it was neccessary to drop the entire table and rebuild it. 
Now you can mark a column as unused (logical delete) or delete it completely (physical delete).

从Oracle 8i开始才能对表中的字段进行drop操作，在这之前只能通过DROP整个表，然后重建来完成这个操作：

Logical Delete
On large tables the process of physically removing a column can be very time and resource consuming. 
For this reason you may decide to logically delete it.

对于大表字段的删除是十分耗时而且消耗资源的，基于这个原因我们可以通过下面的方法来逻辑删除这个表字段：

ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);

--下面我来举个例子
这里有某个表的字段如下：

SQL> desc user_objects_tmp
Name           Type          Nullable Default Comments 
-------------- ------------- -------- ------- -------- 
OBJECT_NAME    VARCHAR2(128) Y                         
SUBOBJECT_NAME VARCHAR2(30)  Y                         
OBJECT_ID      NUMBER        Y                         
DATA_OBJECT_ID NUMBER        Y                         
OBJECT_TYPE    VARCHAR2(19)  Y                         
CREATED        DATE          Y                         
LAST_DDL_TIME  DATE          Y                         
TIMESTAMP      VARCHAR2(19)  Y                         
STATUS         VARCHAR2(7)   Y                         
TEMPORARY      VARCHAR2(1)   Y                         
GENERATED      VARCHAR2(1)   Y                         
SECONDARY      VARCHAR2(1)   Y                         

--我们先将表中的字段设置为不可用
ALTER TABLE user_objects_tmp SET UNUSED(secondary,GENERATED);--TEMPORARY
ALTER TABLE user_objects_tmp SET UNUSED(TEMPORARY);
SQL> desc user_objects_tmp
Name           Type          Nullable Default Comments 
-------------- ------------- -------- ------- -------- 
OBJECT_NAME    VARCHAR2(128) Y                         
SUBOBJECT_NAME VARCHAR2(30)  Y                         
OBJECT_ID      NUMBER        Y                         
DATA_OBJECT_ID NUMBER        Y                         
OBJECT_TYPE    VARCHAR2(19)  Y                         
CREATED        DATE          Y                         
LAST_DDL_TIME  DATE          Y                         
TIMESTAMP      VARCHAR2(19)  Y                         
STATUS         VARCHAR2(7)   Y                         
TEMPORARY      VARCHAR2(1)   Y                         

--可见这两个字段已经查不到了，在下面的表中我们可以知道 USER_OBJECTS_TMP 表有两个字段被置为UNUSED的

SQL> SELECT * FROM DBA_UNUSED_COL_TABS;
 
OWNER                          TABLE_NAME                          COUNT
------------------------------ ------------------------------ ----------
LUBINSU                        USER_OBJECTS_TMP                        2

--要彻底删除这两个字段可以在数据库不繁忙，或者对该表操作较少的时间段进行

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);

--如下所示：这里添加 checkpoint 250 是为了避免回滚段占用过大

ALTER TABLE user_objects_tmp DROP UNUSED COLUMNS CHECKPOINT 250;

Once you user SET UNUSED COLUMN then you cannot get back the column again, 
it is treated as a dropped column. Moreover you can add a new column with that name.
Any time you can drop the SET UNUSED COLUMNS with the following command.

ALTER TABLE table_name DROP UNUSED COLUMNS;
--记住，如果在删除过程中终止操作，这个表是无法访问的！！

SQL> SELECT * FROM user_objects_tmp a WHERE ROWNUM <= 10;
 
SELECT * FROM user_objects_tmp a WHERE ROWNUM <= 10
 
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

--必须删除结束才能访问：

ALTER TABLE user_objects_tmp DROP COLUMNS CONTINUE;

--如果从始至终，并未终止操作，但是该操作正在执行，会发现查不到数据：

SQL> SELECT * FROM user_objects_tmp a WHERE ROWNUM <= 10;
SQL> SELECT * FROM user_objects_tmp a WHERE ROWNUM <= 10;
SQL> 

--另外：表中的字段如果设置为 UNUSED 则无法恢复，但是未DROP之前我们可以重新创建同名字段，
--而如long类型的字段，必须要DROP之后才能创建，每个表中只能有一个long类型的字段

SQL> ALTER TABLE user_objects_tmp SET UNUSED(TIMESTAMP);
 
Table altered
 
SQL> alter table user_objects_tmp add(TIMESTAMP VARCHAR2(19));
 
Table altered
 
SQL> alter table user_objects_tmp add(long_tmp long);
 
Table altered
 
SQL> ALTER TABLE user_objects_tmp SET UNUSED(long_tmp);
 
Table altered
 
SQL> alter table user_objects_tmp add(long_tmp long);
 
alter table user_objects_tmp add(long_tmp long)
 
ORA-01754: a table may contain only one column of type LONG
 
SQL> alter table drop unused columns;
 
alter table drop unused columns
 
ORA-00903: invalid table name
 
SQL> alter table user_objects_tmp drop unused columns;
 
Table altered
 
SQL> alter table user_objects_tmp add(long_tmp long);
 
Table altered
 
SQL> alter table user_objects_tmp add(long_tmp2 long);
 
alter table user_objects_tmp add(long_tmp2 long)
 
ORA-01754: a table may contain only one column of type LONG