最近文章

最近文章
您当前所在位置:主页 > 最近文章 >

删除大表中的字段

发布日期:2018年11月12 编辑:

删除大表中的字段   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之前我们可以重新创建同名字段, --而如long类型的字段,必须要DROP之后才能创建,每个表中只能有一个long类型的字段 [sql]  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    

http://www.bkjia.com/oracle/481108.htmlwww.bkjia.comtruehttp://www.bkjia.com/oracle/481108.htmlTechArticle删除大表中的字段 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 necce...

本文源自: 凯发国际

< 上一篇:ORA-00314,redolog损坏,或丢失处理方法

> 下一篇:没有了