Delete fields from a large table

Delete the field Dropping Columns in a large table delete the field 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 ). the drop operation can be performed on the fields in the table starting from Oracle 8i. Before this operation, you can only DROP the entire table and recreate it to perform this operation: Logical DeleteOn large tables the process of physical Ly removing a column can be very time and resource consuming. for this reason you may decide to logically delete it. deleting a large table field is time-consuming and resource-consuming. For this reason, we can use the following method to logically Delete this table field: [SQL] ALTER TABLE table_name SET UNUSED (column_name); ALTER TABLE table_name SET UNUSED (column_name1, column_name2); -- here is an example where the fields of a TABLE are as follows: [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 1_date y timestamp VARCHAR2 (19) y status VARCHAR2 (7) y temporary VARCHAR2 (1) y generated VARCHAR2 (1) y secondary VARCHAR2 (1) Y [SQL] -- SET the fields in the TABLE to ALTER TABLE user_objects_tmp SET UNUSED (secondary, GENERATED );-- Temporary alter table empty 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 -- the two fields cannot be found, in the following table, we can know that the USER_OBJECTS_TMP table has two fields set to UNUSED [SQL] SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ---------- LUBINSU USER_OBJECTS_TMP 2 -- to completely delete these two fields, you can not busy in the database, alternatively, perform [SQL] ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name DROP (column_name1, column_name2) for a period of time when the TABLE has fewer operations. -- as shown below:: Here, the checkpoint 250 is added to prevent the rollback segment from occupying too much [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; -- remember, if the operation is terminated during the deletion process, the TABLE is Inaccessible !! [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 -- must be deleted to access: [SQL] ALTER TABLE user_objects_tmp DROP COLUMNS CONTINUE; -- the operation is not terminated from begin, but is being executed, no data found: [SQL] SQL> SELECT * FROM user_objects_tmp a where rownum <= 10; SQL> SELECT * FROM user_objects_tmp a where rownum <= 10; SQL> -- In addition: if the field in the table is set to UNUSED, it cannot be restored, but we can re-create a field with the same name before dropping. -- for long fields, you must DROP them before creating them, each TABLE can have only one long field [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