The default, COMPRESS=y, causes Export to flag table data for consolidation into
one initial extent upon import
If you specify COMPRESS=n, Export uses the current storage parameters, including
the values of initial extent size and next extent size.
What is basically says is that if you set compress=y when imported the table size will be same as the exported size. If you set compress=n then things are different as you can see from below.
create table test1 (a char(2000));
begin
for i in 1 .. 20000
loop
insert into test1 values('a'||i);
end loop;
end;
/
SQL> select segment_name,bytes from user_segments where segment_name='TEST1';
SEGMENT_NA BYTES
---------- ----------
TEST1 55574528
SQL> delete from test1;
20000 rows deleted.
SQL> select segment_name,bytes from user_segments where segment_name='TEST1';
SEGMENT_NA BYTES
---------- ----------
TEST1 55574528
Now take two exports one with compress=y and one with compress=n
exp asanga/*** file=test.dmp tables=test1 compress=y
exp asanga/*** file=test_n.dmp tables=test1 compress=n
SQL>drop table test1 purge;
Table dropped.
imp asanga/*** file=test.dmp full=y
Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing ASANGA's objects into ASANGA
. importing ASANGA's objects into ASANGA
. . importing table "TEST1" 0 rows imported
Import terminated successfully without warnings.
SQL>select segment_name,bytes from user_segments where segment_name='TEST1';
SEGMENT_NA BYTES
---------- ----------
TEST1 55574528
SQL>drop table test1 purge;
Table dropped.
imp asanga/asa file=test_n.dmp full=y
Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing ASANGA's objects into ASANGA
. importing ASANGA's objects into ASANGA
. . importing table "TEST1" 0 rows imported
Import terminated successfully without warnings.
SQL>select segment_name,bytes from user_segments where segment_name='TEST1';
SEGMENT_NA BYTES
---------- ----------
TEST1 65536
As it could seen from above even if an empty table is imported with the default options, the size of the table doesn't shrink. If compress=n is used the imported table is redcued in size (or number of segments it uses).
