impdp will do a direct load import. This is one way to confirm
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';
SUM(BYTES/1024/1024)
--------------------
176.1875
SQL> truncate table t;
Table truncated.
SQL> alter table t COMPRESS FOR OLTP ;
Table altered.
Note: compress for all operation is deprecated in 11gR2 instead it will be compress for oltp
SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e xists_action=append Import: Release 11.2.0.1.0 - Production on Thu Oct 14 21:30:33 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning and Real Application Testing options Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA directory=data_pump_d ir dumpfile=xyz.dmp tables='test.t' table_exists_action=append Processing object type TABLE_EXPORT/TABLE/TABLE ORA-39152: Table "TEST"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TEST"."T" 149.2 MB 1689984 rows Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:31:03
Let us check the size now.
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';
SUM(BYTES/1024/1024)
--------------------
72.1875
From 176mb the size is reduced to 72mb. Not let us try the direct load after truncating the table,
SQL> alter table t compress for DIRECT_LOAD OPERATIONS;
Table altered.
SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e
xists_action=append status=2
Import: Release 11.2.0.1.0 - Production on Thu Oct 14 21:32:28 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA directory=data_pump_d
ir dumpfile=xyz.dmp tables='test.t' table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TEST"."T" exists. Data will be appended to existing table but
all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:43:55
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';
SUM(BYTES/1024/1024)
--------------------
72.1875
Now the size has reduced from 176mb
SQL> alter table t nocompress;
Table altered.
SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e
xists_action=append status=2
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';
SUM(BYTES/1024/1024)
--------------------
176.1875
Without compression it’s 176mb.
convert this post to pdf.