CODEWITHSUNDEEP
oracle-databasecompression
radu_cloud
radu_cloud

Reputation: 13

Oracle drop column from compressed table - behind the scenes "set unused"

Working with Oracle 11.2.0.4.0 and trying to drop columns from several OLTP compressed tables, was expecting to get ORA-39726: unsupported add/drop column operation on compressed tables. However, it has run without error but leaving the columns unused.

I was wondering if this behavior could be avoided - I'd like to get the error, in order to avoid confusion (people leaving unused columns all over the place without knowing so).

I could not find references to this, need to boost my searching skills. In the documentation I see only:

You can set unused a column from a table that uses COMPRESS BASIC, but you cannot drop the column. However, all clauses of the drop_column_clause are valid for tables that use COMPRESS FOR OLTP. See the semantics for table_compression for more information. http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2103683

Please do you know any way to configure it to raise the error? The scenario:

create table test_radu_a(col1 number, col2 number) compress for oltp;
alter table test_radu_a drop (col2);
select * from user_unused_col_tabs where table_name = 'TEST_RADU_A';

Upvotes: 1

Views: 1733

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

This seems to be the expected behaviour; from MOS Doc ID 1068820.1:

In 11g it is allowed to drop columns from a compressed table IF compatible is set to 11.1 or higher AND table was created with the "compress for all OLTP" option but even in this situation there is no real drop but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.

Also see documents 1223705.1, 2171802.1 and others. The only way to really drop the column seems to be to uncompress and recompress as shown in document 1987500.1, but that's what's being avoided in the quote above.

There doesn't seem to be any way to get the error you expect.

The closest I think you can get is with a DDL trigger:

create or replace trigger radu_trigger
before alter
on schema
declare
  l_compress_for user_tables.compress_for%type;
begin
  select max(compress_for) into l_compress_for
  from user_tables
  where ora_dict_obj_type = 'TABLE' and table_name = ora_dict_obj_name;

  if l_compress_for is null or l_compress_for != 'OLTP' then
    return;
  end if;

  for r in (
    select column_name from user_tab_columns
    where table_name = ora_dict_obj_name
  )
  loop
    if ora_is_drop_column(r.column_name) then
      raise_application_error(-20001,
        'Do not drop columns from an OLTP-compressed table');
    end if;
  end loop;
end radu_trigger;
/

Then when you try to drop a column on any OLTP-compress table - in that schema - you'll get an error:

alter table test_radu_a drop (col2);

alter table test_radu_a drop (col2)
Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Do not drop columns from an OLTP-compressed table
ORA-06512: at line 18
...

You could look for specific ora_dict_obj_name values if you didn't want to check all compressed tables, of course.

You could mimic the real exception:

create or replace trigger radu_trigger
before alter
on schema
declare
  l_compress_for user_tables.compress_for%type;
  l_exception exception;
  pragma exception_init (l_exception, -39726);
begin
...
  loop
    if ora_is_drop_column(r.column_name) then
      raise l_exception;
    end if;
  end loop;
end radu_trigger;
/

alter table test_radu_a drop (col2);

Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-39726: unsupported add/drop column operation on compressed tables
ORA-06512: at line 20
...

but I think that would be confusing, since the message isn't really true. It's probably safer and cleaner to raise your own bespoke exception.

Upvotes: 1

Related Questions