Does Oracle auto-compress the table as it grows large?

Question

I have a table in my database.Earlier while performing the DROP DDL there was no issue. But after some days as the table become large and i tried DROP DDL it is saying:

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

As per my DBA no commands were run to compress the table.

Answer 1

As per my DBA no commands were run to compress the table.

You could check the compression status by querying [DBA|ALL|USER]_TABLES view.

For example,

SQL> CREATE TABLE t(col1 NUMBER, col2 NUMBER) COMPRESS;

Table created.

SQL> SELECT table_name, compression,compress_for
  2  FROM user_tables
  3  WHERE table_name ='T';

TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ----------------------------
T          ENABLED  BASIC

Dropping the column depends on the type of compression. For a table compressed for direct- path inserts, you cannot drop the column. However, if the table is compressed for all operations, then you can use SET UNUSED/ DROP UNUSED.

How to drop the column in a compressed table

The only way is:

• decompress the entire table
• Make the column unused
• Then drop unused columns.

SQL> ALTER TABLE t SET UNUSED COLUMN ename;

Table altered.

SQL> ALTER TABLE t MOVE NOCOMPRESS PARALLEL 4;

Table altered.

SQL> ALTER TABLE t DROP UNUSED COLUMNS;

Table altered.

SQL> SELECT table_name, compression,compress_for
  2  FROM user_tables
  3  WHERE table_name ='T';

TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ----------------------------
T          DISABLED