删除大表中的字段
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.
对于大表字段的删除是十分耗时而且消耗资源的，基于这个原因我们可以通过下面的方法来逻辑删除这个表字段：
[sql]
ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);
--下面我来举个例子
这里有某个表的字段如下：
[sql]
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
[sql]
--我们先将表中的字段设置为不可用
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]
SQL> SELECT * FROM DBA_UNUSED_COL_TABS;
OWNER TABLE_NAME COUNT
------------------------------ ------------------------------ ----------
LUBINSU USER_OBJECTS_TMP 2
--要彻底删除这两个字段可以在数据库不繁忙，或者对该表操作较少的时间段进行
[sql]
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);
--如下所示：这里添加 checkpoint 250 是为了避免回滚段占用过大
[sql]
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]
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
--必须删除结束才能访问：
[sql]
ALTER TABLE user_objects_tmp DROP COLUMNS CONTINUE;
--如果从始至终，并未终止操作，但是该操作正在执行，会发现查不到数据：
[sql]
SQL> SELECT * FROM user_objects_tmp a WHERE ROWNUM <= 10;
SQL> SELECT * FROM user_objects_tmp a WHERE ROWNUM <= 10;
SQL>
--另外：表中的字段如果设置为 UNUSED 则无法恢复，但是未DROP之前我们可以重新创建
