When trying to drop an Oracle table unused columns, the sql command executes and shows successful, but actually the unused columns are not dropped.
SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';
OWNE TABLE_NAME COUNT
------- -------------------- ----------
TESTER TRANSACTIONS 1
SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;
Table altered.
SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';
OWNE TABLE_NAME COUNT
------- -------------------- ----------
TESTER TRANSACTIONS 1
Further investigation shows the table is created with compression :
SQL>select OWNER, TABLE_NAME, COMPRESSION, COMPRESS_FOR from dba_tables where table_name='TRANSACTIONS';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
-------- ------------ --------- -------------
TESTER TRANSACTIONS ENABLED ADVANCED
Change table to uncompressing status without moving data:
SQL> alter table TRANSACTIONS NOCOMPRESS;
Table altered.
SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;
ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
Have to move table with nocompress option, then drop unused columns successfully.
SQL> alter table TRANSACTIONS move nocompress;
Table altered.
SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;
Table altered.
SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';
no rows selected
Make DBA Life Easy 