Few days back, I was working on one of the UAT environments so as to drop columns from 2 tables which were already set to unused as per request from one of my application team.
Note: For identifying unused columns for any table, you need to query dba_tab_cols view as follows. If count is greater than zero then table has unused columns in it. This columns will not be visible in view dba_tab_columns.
select count(0) from dba_tab_cols where table_name=<table_name> and column_name like ‘SYS%$’;
Both the tables were compressed one & as per Oracle’s documentation, Compressed tables do not support drop columns.
So if I need drop these unused columns then , I will need to follow simple approach of uncompessing tables first, then dropping unused columns followed by compressing it back.
This approach worked well for first table. 🙂
SQL> select count(0) from dba_tab_cols where table_name='LPP_TMP' and column_name like 'SYS%$'; COUNT(0) ---------- 2 SQL> ALTER TABLE TEST1.LPP_TMP DROP UNUSED COLUMNS; Table altered. SQL> select count(0) from dba_tab_cols where table_name='LPP_TMP' and column_name like 'SYS%$'; COUNT(0) ---------- 2 SQL> ALTER TABLE TEST1.LPP_TMP nocompress; Table altered. SQL> ALTER TABLE TEST1.LPP_TMP DROP UNUSED COLUMNS; Table altered. SQL> select count(0) from dba_tab_cols where table_name='LPP_TMP' and column_name like 'SYS%$'; COUNT(0) ---------- 0 SQL> ALTER TABLE TEST1.LPP_TMP compress; Table altered.
Though it worked well for first table, it gave me “ORA-39726: unsupported add/drop column operation on compressed tables“ while applying same strategy on second table. 😦
SQL> ALTER TABLE TEST1.LPPF_TMP nocompress; Table altered. SQL> ALTER TABLE TEST1.LPPF_TMP DROP UNUSED COLUMNS; ALTER TABLE TEST1.LPPF_TMP DROP UNUSED COLUMNS * ERROR at line 1: ORA-39726: unsupported add/drop column operation on compressed tables
After searching on metalink I found note: ORA-39726: Clarification on error when Drop Unused Column does not help (Doc ID 1574318.1), which indicates to use “table move” or “table redefinition” to make the table completely uncompressed, but still you cannot drop the column.
I still tried workaround of moving table first before uncompressing it & it worked for me 🙂
SQL> select count(0) from dba_tab_cols where table_name='LPPF_TMP' and column_name like 'SYS%$'; COUNT(0) ---------- 2 SQL> ALTER TABLE TEST1.LPPF_TMP move; Table altered. SQL> ALTER TABLE TEST1.LPPF_TMP nocompress; Table altered. SQL> ALTER TABLE TEST1.LPPF_TMP DROP UNUSED COLUMNS; Table altered. SQL> select count(0) from dba_tab_cols where table_name='LPPF_TMP' and column_name like 'SYS%$'; COUNT(0) ---------- 0 SQL> ALTER TABLE TEST1.LPPF_TMP compress; Table altered.
Hope so u will find this post very useful 🙂
Cheers
Regards,
Adityanath
Adityanath's Database & Cloud Blog 
Leave a comment