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
Categories: Administration, Advanced features, ORA errors
Adityanath's Oracle Blog 
Good writing. Cheers
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
But wanna comment that you have a very nice site, I like the style and design it actually stands out.
very interesting details you have observed, appreciate it for putting up.
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.
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!
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.
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.
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!
I’m often to running a blog and i really recognize your content. The article has actually peaks my interest. I am going to bookmark your web site and maintain checking for new information.
What’s up, after reading this amazing post i am as well cheerful to share my experience here with colleagues.
Greetings from California! I’m bored to tears at work so I decided to check out your blog on my iphone during lunch break. I love the knowledge you provide here and can’t wait to take a look when I get home. I’m amazed at how quick your blog loaded on my cell phone .. I’m not even using WIFI, just 3G .. Anyways, amazing site!
Nice blog! Is your theme custom made or did you download it from somewhere? A design like yours with a few simple adjustements would really make my blog shine. Please let me know where you got your design. Thanks
Hello! This is my first visit to your blog! We are a collection of volunteers and starting a new initiative in a community in the same niche. Your blog provided us valuable information to work on. You have done a outstanding job!
What’s Going down i’m new to this, I stumbled upon this I’ve found It absolutely useful and it has helped me
out loads. I am hoping to contribute & aid other users like its helped me.
Good job.
Howdy! Do you use Twitter? I’d like to follow you if that would be ok. I’m definitely enjoying your blog and look forward to new updates.