ORA-39726: unsupported add/drop column operation on compressed tables

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

9 replies

  1. What’s up everyone, it’s my first pay a quick visit at this website, and article is actually fruitful in favor of me, keep
    up posting these types of articles.

  2. Wow, superb weblog format! How lengthy have you ever been blogging for? you make blogging look easy. The full look of your web site is magnificent, let alone the content!

  3. I’m more than happy to discover this site. I want to to thank you for your time for this particularly wonderful read!! I definitely really liked every bit of it and i also have you saved as a favorite to look at new stuff on your site.

  4. If some one wants to be updated with most recent technologies
    after that he must be visit this site and be up to date all the time.

  5. I think other site proprietors should take this site as an model, very clean and wonderful user friendly style and design, as well as the content. You’re an expert in this topic!

Leave a Reply to Stephine Greenwell Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s