A friend asked to check the
ISSUE:
ORA-39726 is hit when adding column with default value to basic compressed table.
CAUSE:
compressed table will not allow some operation, which are allowed in uncompressed table.
"Is there any error during add column with default value to table compressed with HCC ?
Does Oracle unpack HCC data during add column with default value to the table compressed with HCC ?" he asked.
----------------------------------------------------------
And the answer is here.
RDBMS version is 12.1.0.2.171017 (i choosed old enough version i can find. newer versions belived work the same).
$ opatch lspatches
26635845;Database PSU 12.1.0.2.171017, Oracle JavaVM Component (OCT2017)
25729214;OCW Interim patch for 25729214
26717470;Database Bundle Patch : 12.1.0.2.171017 (26717470)
$ sqlplus / as sysdba
SQL> create tablespace yu extent management local segment space management auto;
SQL> create user yu identified by yu default tablespace yu;
SQL> grant dba to yu ;
SQL> conn yu/yu
SQL> create table NOCOMP as select owner,object_type,object_name,object_id,created from dba_objects order by owner,object_type,object_name;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
commit;
Table created.
SQL>
151156 rows created.
SQL>
302312 rows created.
SQL>
604624 rows created.
SQL>
1209248 rows created.
SQL>
Commit complete.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------
NOCOMP
SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 1;
SEGMENT_NAME BYTES MB BLOCKS
-------------- ---------- ------- ----------
NOCOMP 159383552 152 9728
SQL> create table BASICOMP compress as select * from NOCOMP;
create table OLTPCOMP compress for OLTP as select * from NOCOMP;
create table QL compress for query LOW as select * from NOCOMP;
create table QLRL compress for query LOW row level locking as select * from NOCOMP;
create table QH compress for query HIGH as select * from NOCOMP;
create table QHRL compress for query HIGH row level locking as select * from NOCOMP;
create table AL compress for archive LOW as select * from NOCOMP;
create table ALRL compress for archive LOW row level locking as select * from NOCOMP;
create table AH compress for archive HIGH as select * from NOCOMP;
create table AHRL compress for archive HIGH row level locking as select * from NOCOMP;
Table created.
SQL>
Table created.
SQL> SQL>
Table created.
SQL>
Table created.
SQL> SQL>
Table created.
SQL>
Table created.
SQL> SQL>
Table created.
SQL>
Table created.
SQL> SQL>
Table created.
SQL>
Table created.
SQL> exec dbms_stats.gather_schema_stats('YU');
PL/SQL procedure successfully completed.
SQL> col segment_name for a8
SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 2 desc;
SEGMENT_ BYTES MB BLOCKS
-------- ---------- ---------- ---------
NOCOMP 159383552 152 9728
OLTPCOMP 100663296 96 6144
BASICOMP 92274688 88 5632
QLRL 47185920 45 2880
QL 44040192 42 2688
QHRL 26214400 25 1600
ALRL 25165824 24 1536
QH 24117248 23 1472
AL 23068672 22 1408
AHRL 18874368 18 1152
AH 16777216 16 1024
SQL> col table_name for a8
SQL> select table_name,num_rows,blocks from user_tables order by blocks desc;
TABLE_NA NUM_ROWS BLOCKS
-------- ---------- ---------
NOCOMP 2418496 9652
OLTPCOMP 2418496 5799
BASICOMP 2418496 5211
QLRL 2418496 2848
QL 2418496 2670
QHRL 2418496 1577
ALRL 2418496 1492
QH 2418496 1456
AL 2418496 1400
AHRL 2418496 1148
AH 2418496 1010
SQL>
alter table NOCOMP add new_column number(3) default 0;
alter table BASICOMP add new_column number(3) default 0;
alter table OLTPCOMP add new_column number(3) default 0;
alter table QLRL add new_column number(3) default 0;
alter table QL add new_column number(3) default 0;
alter table QHRL add new_column number(3) default 0;
alter table ALRL add new_column number(3) default 0;
alter table QH add new_column number(3) default 0;
alter table AL add new_column number(3) default 0;
alter table AHRL add new_column number(3) default 0;
alter table AH add new_column number(3) default 0;
Table altered.
alter table BASICOMP add new_column number(3) default 0
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL> exec dbms_stats.gather_schema_stats('YU');
PL/SQL procedure successfully completed.
SQL> col segment_name for a8
SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 2 desc;
SEGMENT_ BYTES MB BLOCKS BLOCKS before add column
-------- ---------- ------- ------- --------------------------
NOCOMP 159383552 152 9728 9728
OLTPCOMP 100663296 96 6144 6144
BASICOMP 92274688 88 5632 5632
QLRL 47185920 45 2880 2880
QL 44040192 42 2688 2688
QHRL 26214400 25 1600 1600
ALRL 25165824 24 1536 1536
QH 24117248 23 1472 1472
AL 23068672 22 1408 1408
AHRL 18874368 18 1152 1152
AH 16777216 16 1024 1024
SQL> col table_name for a8
SQL> select table_name,num_rows,blocks from user_tables order by blocks desc;
TABLE_NA NUM_ROWS BLOCKS BLOCKS before add column
-------- ---------- ---------- ------------------------
NOCOMP 2418496 9652 9652
OLTPCOMP 2418496 5799 5799
BASICOMP 2418496 5211 5211
QLRL 2418496 2848 2848
QL 2418496 2670 2670
QHRL 2418496 1577 1577
ALRL 2418496 1492 1492
QH 2418496 1456 1456
AL 2418496 1400 1400
AHRL 2418496 1148 1148
AH 2418496 1010 1010
No one block is added after ADD COLUMN modification!
As you can see the only issue for BASIC COMPRESSION:
alter table BASICOMP add new_column number(3) default 0
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
In other cases "alter table add column" work well: no decompression at all.
As the DOC 12.1.0.2 say:
"... the default value is stored as metadata, the column itself is not populated with data"
ISSUE:
ORA-39726 is hit when adding column with default value to basic compressed table.
CAUSE:
compressed table will not allow some operation, which are allowed in uncompressed table.
"Is there any error during add column with default value to table compressed with HCC ?
Does Oracle unpack HCC data during add column with default value to the table compressed with HCC ?" he asked.
----------------------------------------------------------
And the answer is here.
RDBMS version is 12.1.0.2.171017 (i choosed old enough version i can find. newer versions belived work the same).
$ opatch lspatches
26635845;Database PSU 12.1.0.2.171017, Oracle JavaVM Component (OCT2017)
25729214;OCW Interim patch for 25729214
26717470;Database Bundle Patch : 12.1.0.2.171017 (26717470)
$ sqlplus / as sysdba
SQL> create tablespace yu extent management local segment space management auto;
SQL> create user yu identified by yu default tablespace yu;
SQL> grant dba to yu ;
SQL> conn yu/yu
SQL> create table NOCOMP as select owner,object_type,object_name,object_id,created from dba_objects order by owner,object_type,object_name;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
commit;
Table created.
SQL>
151156 rows created.
SQL>
302312 rows created.
SQL>
604624 rows created.
SQL>
1209248 rows created.
SQL>
Commit complete.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------
NOCOMP
SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 1;
SEGMENT_NAME BYTES MB BLOCKS
-------------- ---------- ------- ----------
NOCOMP 159383552 152 9728
SQL> create table BASICOMP compress as select * from NOCOMP;
create table OLTPCOMP compress for OLTP as select * from NOCOMP;
create table QL compress for query LOW as select * from NOCOMP;
create table QLRL compress for query LOW row level locking as select * from NOCOMP;
create table QH compress for query HIGH as select * from NOCOMP;
create table QHRL compress for query HIGH row level locking as select * from NOCOMP;
create table AL compress for archive LOW as select * from NOCOMP;
create table ALRL compress for archive LOW row level locking as select * from NOCOMP;
create table AH compress for archive HIGH as select * from NOCOMP;
create table AHRL compress for archive HIGH row level locking as select * from NOCOMP;
Table created.
SQL>
Table created.
SQL> SQL>
Table created.
SQL>
Table created.
SQL> SQL>
Table created.
SQL>
Table created.
SQL> SQL>
Table created.
SQL>
Table created.
SQL> SQL>
Table created.
SQL>
Table created.
SQL> exec dbms_stats.gather_schema_stats('YU');
PL/SQL procedure successfully completed.
SQL> col segment_name for a8
SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 2 desc;
SEGMENT_ BYTES MB BLOCKS
-------- ---------- ---------- ---------
NOCOMP 159383552 152 9728
OLTPCOMP 100663296 96 6144
BASICOMP 92274688 88 5632
QLRL 47185920 45 2880
QL 44040192 42 2688
QHRL 26214400 25 1600
ALRL 25165824 24 1536
QH 24117248 23 1472
AL 23068672 22 1408
AHRL 18874368 18 1152
AH 16777216 16 1024
SQL> col table_name for a8
SQL> select table_name,num_rows,blocks from user_tables order by blocks desc;
TABLE_NA NUM_ROWS BLOCKS
-------- ---------- ---------
NOCOMP 2418496 9652
OLTPCOMP 2418496 5799
BASICOMP 2418496 5211
QLRL 2418496 2848
QL 2418496 2670
QHRL 2418496 1577
ALRL 2418496 1492
QH 2418496 1456
AL 2418496 1400
AHRL 2418496 1148
AH 2418496 1010
SQL>
alter table NOCOMP add new_column number(3) default 0;
alter table BASICOMP add new_column number(3) default 0;
alter table OLTPCOMP add new_column number(3) default 0;
alter table QLRL add new_column number(3) default 0;
alter table QL add new_column number(3) default 0;
alter table QHRL add new_column number(3) default 0;
alter table ALRL add new_column number(3) default 0;
alter table QH add new_column number(3) default 0;
alter table AL add new_column number(3) default 0;
alter table AHRL add new_column number(3) default 0;
alter table AH add new_column number(3) default 0;
Table altered.
alter table BASICOMP add new_column number(3) default 0
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL> exec dbms_stats.gather_schema_stats('YU');
PL/SQL procedure successfully completed.
SQL> col segment_name for a8
SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 2 desc;
SEGMENT_ BYTES MB BLOCKS BLOCKS before add column
-------- ---------- ------- ------- --------------------------
NOCOMP 159383552 152 9728 9728
OLTPCOMP 100663296 96 6144 6144
BASICOMP 92274688 88 5632 5632
QLRL 47185920 45 2880 2880
QL 44040192 42 2688 2688
QHRL 26214400 25 1600 1600
ALRL 25165824 24 1536 1536
QH 24117248 23 1472 1472
AL 23068672 22 1408 1408
AHRL 18874368 18 1152 1152
AH 16777216 16 1024 1024
SQL> col table_name for a8
SQL> select table_name,num_rows,blocks from user_tables order by blocks desc;
TABLE_NA NUM_ROWS BLOCKS BLOCKS before add column
-------- ---------- ---------- ------------------------
NOCOMP 2418496 9652 9652
OLTPCOMP 2418496 5799 5799
BASICOMP 2418496 5211 5211
QLRL 2418496 2848 2848
QL 2418496 2670 2670
QHRL 2418496 1577 1577
ALRL 2418496 1492 1492
QH 2418496 1456 1456
AL 2418496 1400 1400
AHRL 2418496 1148 1148
AH 2418496 1010 1010
No one block is added after ADD COLUMN modification!
As you can see the only issue for BASIC COMPRESSION:
alter table BASICOMP add new_column number(3) default 0
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
In other cases "alter table add column" work well: no decompression at all.
As the DOC 12.1.0.2 say:
"... the default value is stored as metadata, the column itself is not populated with data"
