My Oracle Support Banner

Partitioned Table With More Than 255 Columns Will Not Compress In Oracle 12.1 (Doc ID 2337630.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

Symptoms

In our production database, there is a table with more than 255 columns. The database in production is currently Oracle11. The restriction there is 255 columns for Advanced Compression. In our test environment, we have imported one partition from the production table and imported this into an Oracle12 database. We can't compress the data from this partition. The size of the partition stays the same after the compression.

SQL> select seg.owner,
seg.table_name,
2 3 seg.partition_name,
4 seg.SIZE_Mb,
5 b.num_rows,
6 b.last_analyzed,
7 b.blocks,
8 b.compression,
9 b.compress_for
10 from (select a.owner,
11 a.segment_name table_name,
12 a.PARTITION_NAME,
13 round(sum((a.bytes / 1024 / 1024))) SIZE_Mb
14 from sys.dba_segments a
15 where a.segment_type in ('TABLE PARTITION')
16 and a.owner = 'DM'
17 and a.segment_name like 'ERW%'
18 group by a.owner, a.segment_name, a.PARTITION_NAME) seg,
19 dba_tab_partitions b
20 where seg.owner = b.table_owner
21 and seg.table_name = b.table_name
22 and seg.partition_name = b.partition_name
23 order by seg.owner, seg.table_name, seg.partition_name;

OWNER TABLE_NAME PARTITION_NAME SIZE_MB NUM_ROWS LAST_ANAL BLOCKS COMPRESS COMPRESS_FOR
---------- -------------------- -------------------- ---------- ---------- ------------------- -------- ------------- ---------------
DM ERW P201304 80 100000 26-SEP-17 9635 DISABLED

SQL> alter table dm.erw move partition P201304 row store compress advanced;

Table altered.

SQL> select seg.owner,
2 seg.table_name,
3 seg.partition_name,
4 seg.SIZE_Mb,
5 b.num_rows,
6 b.last_analyzed,
7 b.blocks,
8 b.compression,
9 b.compress_for
10 from (select a.owner,
11 a.segment_name table_name,
12 a.PARTITION_NAME,
13 round(sum((a.bytes / 1024 / 1024))) SIZE_Mb
14 from sys.dba_segments a
15 where a.segment_type in ('TABLE PARTITION')
16 and a.owner = 'DM'
17 and a.segment_name like 'ERW%'
18 group by a.owner, a.segment_name, a.PARTITION_NAME) seg,
19 dba_tab_partitions b
20 where seg.owner = b.table_owner
21 and seg.table_name = b.table_name
22 and seg.partition_name = b.partition_name
23 order by seg.owner, seg.table_name, seg.partition_name;

OWNER TABLE_NAME PARTITION_NAME SIZE_MB NUM_ROWS LAST_ANAL BLOCKS COMPRESS COMPRESS_FOR
---------- -------------------- -------------------- ---------- ---------- --------- ---------- -------- ------------------------------
DM ERW P201304 80 100000 26-SEP-17 9635 ENABLED ADVANCED

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.