Sometimes DBA got request to drop the table’s columns. Oracle has following options to complete this task.
- Logical Delete.
- Physical Delete.
- Claim space for logical delete columns.
- Claim space for Physical delete column.
- Conclusion.
In this article, I will discuss which option is more appropriate for big size of table. For small table, even physical delete will not take take much time or resource.
Logical Delete:
Logical delete means oracle dictionary table will update and ignore the column so the column will no longer be visible to the user and no option to rollback for user. Example:
ALTER TABLE table_name SET UNUSED (column_name); ALTER TABLE table_name SET UNUSED (column_name1, column_name2);
No space release after made unused for a column until you are recreating the table.
SQL> CONN SAMAD
Enter password:
Connected.
SQL>
SQL> CREATE TABLE SAMAD.TBL_DROP_DEMO AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME ='TBL_DROP_DEMO';
BLOCKS EMPTY_BLOCKS
---------- ------------
1376 0
SQL> ALTER TABLE SAMAD.TBL_DROP_DEMO SET UNUSED COLUMN OBJECT_NAME;
Table altered.
SQL> SELECT COUNT(*) FROM USER_UNUSED_COL_TABS
WHERE TABLE_NAME ='TBL_DROP_DEMO';
COUNT(*)
----------
1
SQL> SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS
WHERE TABLE_NAME ='TBL_DROP_DEMO';
SQL> SET LINESIZE 100
SQL> COL COLUMN_NAME FOR A30
SQL> COL HIDDEN_COLUMN FOR A20
SQL> SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS
WHERE TABLE_NAME ='TBL_DROP_DEMO';
COLUMN_NAME HIDDEN_COLUMN
------------------------------ --------------------
OWNER NO
SYS_C00002_20102919:10:53$ YES
SUBOBJECT_NAME NO
OBJECT_ID NO
DATA_OBJECT_ID NO
... ...
MODIFIED_VSNID NO
26 rows selected.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAMAD', 'TBL_DROP_DEMO');
PL/SQL procedure successfully completed.
SQL> SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME ='TBL_DROP_DEMO';
BLOCKS EMPTY_BLOCKS
---------- ------------
1376 0
Physical Delete:
Physically removing a column from a table, means it will modify all blocks and rows and will make free list for deleted column. On large tables the process of physically removing a column can be very time and resource consuming.
Syntax to drop column:
ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name DROP (column_name1, column_name2);
No space release after dropped a column until you are recreating/moving the table.
SQL> ALTER TABLE SAMAD.TBL_DROP_DEMO DROP UNUSED COLUMNS;
Table altered.
SQL> SELECT COUNT(*) FROM USER_UNUSED_COL_TABS
WHERE TABLE_NAME ='TBL_DROP_DEMO';
COUNT(*)
----------
0
SQL> SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS
WHERE TABLE_NAME ='TBL_DROP_DEMO';
COLUMN_NAME HIDDEN_COLUMN
------------------------------ --------------------
OWNER NO
SUBOBJECT_NAME NO
OBJECT_ID NO
DATA_OBJECT_ID NO
OBJECT_TYPE NO
CREATED NO
... ....
CREATED_VSNID NO
MODIFIED_APPID NO
MODIFIED_VSNID NO
25 rows selected.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAMAD', 'TBL_DROP_DEMO');
PL/SQL procedure successfully completed.
SQL> SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME ='TBL_DROP_DEMO';
BLOCKS EMPTY_BLOCKS
---------- ------------
1376 0
Claim space for logical delete columns:
SQL> CREATE TABLE SAMAD.TBL_DROP_DEMO AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> ALTER TABLE SAMAD.TBL_DROP_DEMO SET UNUSED COLUMN OBJECT_NAME;
Table altered.
SQL> SELECT COUNT(*) FROM USER_UNUSED_COL_TABS
WHERE TABLE_NAME ='TBL_DROP_DEMO';
COUNT(*)
----------
1
SQL> SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS
WHERE TABLE_NAME ='TBL_DROP_DEMO';
COLUMN_NAME HIDDEN_COLUMN
------------------------------ --------------------
OWNER NO
SYS_C00002_20102921:25:48$ YES
SUBOBJECT_NAME NO
OBJECT_ID NO
DATA_OBJECT_ID NO
... ...
CREATED_VSNID NO
MODIFIED_APPID NO
MODIFIED_VSNID NO
26 rows selected.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAMAD', 'TBL_DROP_DEMO');
PL/SQL procedure successfully completed.
SQL> SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME ='TBL_DROP_DEMO';
BLOCKS EMPTY_BLOCKS
---------- ------------
1376 0
SQL> ALTER TABLE SAMAD.TBL_DROP_DEMO MOVE;
Table altered.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAMAD', 'TBL_DROP_DEMO');
PL/SQL procedure successfully completed.
SQL> SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME ='TBL_DROP_DEMO';
BLOCKS EMPTY_BLOCKS
---------- ------------
1111 0
Claim space for Physical delete column:
If column size is significantly big on table, then we may think about free space reused after column dropped. If PCT free is not enough to hold a record then you may need to move the table to release all free space.
SQL> ALTER TABLE SAMAD.TBL_DROP_DEMO DROP COLUMN OBJECT_NAME;
Table altered.
Or,
SQL> ALTER TABLE SAMAD.TBL_DROP_DEMO DROP UNUSED COLUMNS;
Table altered.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAMAD', 'TBL_DROP_DEMO');
PL/SQL procedure successfully completed.
SQL> SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME ='TBL_DROP_DEMO';
BLOCKS EMPTY_BLOCKS
---------- ------------
1376 0
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SAMAD', 'TBL_DROP_DEMO');
PL/SQL procedure successfully completed.
SQL> SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME ='TBL_DROP_DEMO';
BLOCKS EMPTY_BLOCKS
---------- ------------
1104 0
Undo management is a challenge for big table alter:
Undo TBS management is one if the challenge for dropping the columns on large tables. We can control or reduce undo generation by using the CHECKPOINT option which forces a checkpoint after the specified number of rows have been processed.
SQL> ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 200;
Conclusion:
If table size is huge let say 200 GB, it may take couple of hours to drop a column and during this time table will be unavailable. It doesn’t matter how you are deleting the column/columns from a table. If business can’t effort the downtime for dropping the column and impact of moving of the table, then logical delete is better and safe option.
