Import Bypasses Table Compression or Does Not Compress if the Table is Precreated as Compressed (Doc ID 341805.1)

Last updated on JUNE 04, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Symptoms

Original import utility bypasses the table compression or does not compress, if the table is precreated as compressed. Please follow the next example that demonstrates this.

connect / as sysdba
create tablespace tbs_compress datafile '/tmp/tbs_compress01.dbf' size 100m;
create user test identified by test default tablespace tbs_compress temporary tablespace temp;
grant connect, resource to test;

connect test/test

-- create compressed table
create table compressed
(
   id   number,
   text varchar2(100)
) pctfree 0 pctused 90 compress;

-- create non-compressed table
create table noncompressed
(
   id   number,
   text varchar2(100)
) pctfree 0 pctused 90 nocompress;

-- populate compressed table with data
begin
  for i in 1..100000 loop
    insert into compressed values (1, lpad ('1', 100, '0'));
  end loop;
  commit;
end;
/

-- populate non-compressed table with identical data
begin
  for i in 1..100000 loop
    insert into noncompressed values (1, lpad ('1', 100, '0'));
  end loop;
  commit;
end;
/

-- compress the table COMPRESSED (previous insert doesn't use the compression)
alter table compressed move compress;

Let's now take a look at data dictionary to see the differences between the two tables:

connect test/test
select dbms_metadata.get_ddl ('TABLE', 'COMPRESSED') from dual;

DBMS_METADATA.GET_DDL('TABLE','COMPRESSED')
----------------------------------------------------------------------
CREATE TABLE "TEST"."COMPRESSED"
( "ID" NUMBER,
  "TEXT" VARCHAR2(100)
) PCTFREE 0 PCTUSED 90 INITRANS 1 MAXTRANS 255 COMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_COMPRESS"

1 row selected.

SQL> select dbms_metadata.get_ddl ('TABLE', 'NONCOMPRESSED') from dual;

DBMS_METADATA.GET_DDL('TABLE','NONCOMPRESSED')
----------------------------------------------------------------------
CREATE TABLE "TEST"."NONCOMPRESSED"
( "ID" NUMBER,
  "TEXT" VARCHAR2(100)
) PCTFREE 0 PCTUSED 90 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_COMPRESS"

1 row selected.

col segment_name format a30
select segment_name, bytes, extents, blocks from user_segments;

SEGMENT_NAME                   BYTES      EXTENTS    BLOCKS
------------------------------ ---------- ---------- ----------
COMPRESSED                        2097152         17        256
NONCOMPRESSED                    11534336         26       1408

2 rows selected.

The table COMPRESSED needs fewer storage space than the table NONCOMPRESSED. Now, let's export the tables using the original export utility:

#> exp test/test file=test_compress.dmp tables=compressed,noncompressed compress=n

...
About to export specified tables via Conventional Path ...
. . exporting table COMPRESSED        100000 rows exported
. . exporting table NONCOMPRESSED     100000 rows exported
Export terminated successfully without warnings.


and then import them back:

connect test/test
drop table compressed;
drop table noncompressed;

#> imp test/test file=test_compress.dmp tables=compressed,noncompressed

...
. importing TEST's objects into TEST
. . importing table "COMPRESSED"       100000 rows imported
. . importing table "NONCOMPRESSED"    100000 rows imported
Import terminated successfully without warnings.

Verify the extents after original import:

col segment_name format a30
select segment_name, bytes, extents, blocks from user_segments;

SEGMENT_NAME                   BYTES      EXTENTS    BLOCKS
------------------------------ ---------- ---------- ----------
COMPRESSED                       11534336         26       1408
NONCOMPRESSED                    11534336         26       1408

2 rows selected.

=> The table compression is gone.

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