Oracle Fails to Drop Unused Columns

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

Oracle Enterprise Manager Corrective Action (CA) Execution Stats

How many times Corrective Action (CA) has been executed one year so far ?

SQL> select CA_NAME,  count(*) 
       from sysman.MGMT$CA_EXECUTIONS 
      where START_TIME>=sysdate-365  
   group by CA_NAME order by 1

CA_NAME                                  COUNT(*)
---------------------------------------- ----------
...
..
.
EXTEND_TABLESPACE_NORAC                         3
EXTEND_TABLESPACE_PDB                          16
EXTEND_TABLESPACE_RAC                          70
NON-RAC_RMAN_AUTO_BACKUP_ARCHIVELOGS           56
RMAN_AUTO_BACKUP_ARCHIVELOGS                  533
...
..
.

10 rows selected.

About view MGMT$CA_EXECUTIONS , Oracle official documentation is here or just click the following link.

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.5/emvws/job-views.html#GUID-CBDDC556-498B-4458-BCB4-CF28CAB3A834

How to Upgrade AWS RDS Microsoft SQL Server DB Engine

This blog demonstrates how to do minor version upgrades and major version upgrades against SQL server RDS on AWS.

This post also addresses some errors like ‘InvalidParameterCombination’, etc.

Subscribe to get access

Read more of this content when you subscribe today.