CODEWITHSUNDEEP
oracle-databaseoracle11g
kattashri
kattashri

Reputation: 267

Oracle plsql ORA-39726 error

I have my plsql block as below:

DECLARE
CURSOR cdrimei IS select distinct(table_name) as tname from user_tab_columns 
where column_name = 'CALLED_NUMBER_TIPO_ABONADO' and table_name not like '%_V' order by table_name;
BEGIN
FOR i in cdrimei
LOOP

execute immediate 'ALTER TABLE '||i.tname||' DROP (IMEI_CHAIN ,IMEI_STOLEN)';
execute immediate 'ALTER TABLE '||i.tname||' ADD (IMEI_CHAIN varchar2(255),IMEI_STOLEN varchar2(255))';

END LOOP;
END;
/

After executing this I am facing the below error:

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

I don't understand the meaning of compressed tables.

Upvotes: 1

Views: 2720

Answers (1)

Marco Baldelli
Marco Baldelli

Reputation: 3728

Compressed tables have some limitations documented in the Administrator's Guide:

The following restrictions apply when adding columns to compressed tables:

  • Basic compression—You cannot specify a default value for an added column.

  • OLTP compression—If a default value is specified for an added column, then the column must be NOT NULL. Added nullable columns with default values are not supported.

The following restrictions apply when dropping columns in compressed tables:

  • Basic compression—Dropping a column is not supported.

  • OLTP compression—DROP COLUMN is supported, but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.

Upvotes: 2

Related Questions