Table Level COMPRESS Is Ignored By Data Pump If The Table Has 255 Columns (Doc ID 738729.1)

Last updated on OCTOBER 09, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

Datapump Export and Import are not able to use the table level COMPRESS feature when the table contains 255 columns.

Even if the COMPRESS FOR ALL OPERATIONS option is set for a table, the table is not compressed at import when the table has exact 255 columns.

After importing a table with 255 columns which has compression enabled, the amount of extents and bytes occupied by the segment is the same as in case of NOCOMPRESS. The table compression works in case the table is populated using INSERT statements from within e.g. SQL*Plus.

The CREATE TABLE description in the Oracle11g SQL Reference states:

Restrictions on Table Compression

Table compression is subject to the following restrictions:

* Table compression is not supported for tables with more than 255 columns.


 
According to the Oracle documentation the table compression feature should work when the table contains exactly 255 columns.

The COMPRESS table level feature is handled correctly by EXPDP/IMPDP when the table contains 254 columns. As EXPDP/IMPDP uses either direct path INSERTs or external tables, compression of the table results in faster table access after import when the table was originally populated using conventional path INSERTs.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms