Table Level COMPRESS Is Ignored By Data Pump If The Table Has 255 Columns
Last updated on OCTOBER 09, 2013
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 11.1]
Information in this document applies to any platform.
This problem can occur on any platform.
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:
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.
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