impdp Datapump Compression

How to Apply Compression During Data Pump Import ?



create table bigtab nologging as
select level id,
       cast('OWNER_' || mod(level, 100) as varchar2(128)) as owner,
       cast('OBJECT_' || level as varchar2(128)) as object_name,
       cast(null as varchar2(128)) as subobject_name,
       cast(level as number) as object_id,
       cast(null as number) as data_object_id,
       cast('TABLE' as varchar2(30)) as object_type,
       sysdate as created,
       sysdate as last_ddl_time,
       cast(null as varchar2(30)) as timestamp,
       cast('VALID' as varchar2(30)) as status,
       cast('N' as varchar2(1)) as temporary,
       cast('N' as varchar2(1)) as generated,
       cast('N' as varchar2(1)) as secondary
  from dual
 connect by level <= 1000000;

Table created.

SQL> SQL> select count(*) from bigtab ;

  COUNT(*)
----------
   1000000


col SEGMENT_NAME for a20
SELECT segment_name, 
       segment_type, 
       bytes / 1024 / 1024 AS size_mb,
       bytes / 1024 / 1024 / 1024 AS size_gb
  FROM user_segments
 WHERE segment_name = UPPER('bigtab');
 
 SEGMENT_NAME         SEGMENT_TYPE          SIZE_MB    SIZE_GB
-------------------- ------------------ ---------- ----------
BIGTAB               TABLE                      88   .0859375


expdp 


CREATE OR REPLACE DIRECTORY pdb_exp_dir AS '/home/oracle';



expdp vihaan/vihaan123@192.168.1.197/EMPDBREPOS \
DIRECTORY=pdb_exp_dir \
DUMPFILE=BIGTAB.dmp \
LOGFILE=BIGTAB.log \
TABLES=vihaan.BIGTAB

 

[oracle@ORAOEM ~]$ expdp vihaan/vihaan123@192.168.1.197/EMPDBREPOS \
> DIRECTORY=pdb_exp_dir \
> DUMPFILE=BIGTAB.dmp \
> LOGFILE=BIGTAB.log \
> TABLES=vihaan.BIGTAB

Export: Release 19.0.0.0.0 - Production on Fri Nov 6 08:07:59 2026
Version 19.32.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "VIHAAN"."SYS_EXPORT_TABLE_01":  vihaan/********@192.168.1.197/EMPDBREPOS DIRECTORY=pdb_exp_dir DUMPFILE=BIGTAB.dmp LOGFILE=BIGTAB.log TABLES=vihaan.BIGTAB
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "VIHAAN"."BIGTAB"                           70.34 MB 1000000 rows
Master table "VIHAAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for VIHAAN.SYS_EXPORT_TABLE_01 is:
  /home/oracle/BIGTAB.dmp
Job "VIHAAN"."SYS_EXPORT_TABLE_01" successfully completed at Fri Nov 6 08:08:22 2026 elapsed 0 00:00:19

[oracle@ORAOEM ~]$




SQL> drop table BIGTAB ;

Table dropped.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.32.0.0.0
[oracle@ORAOEM ~]$ impdp vihaan/vihaan123@192.168.1.197/EMPDBREPOS directory=pdb_exp_dir dumpfile=BIGTAB.dmp tables=vihaan.BIGTAB TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"ROW\ STORE\ COMPRESS\ ADVANCED\"

Import: Release 19.0.0.0.0 - Production on Fri Nov 6 08:15:55 2026
Version 19.32.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "VIHAAN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "VIHAAN"."SYS_IMPORT_TABLE_01":  vihaan/********@192.168.1.197/EMPDBREPOS directory=pdb_exp_dir dumpfile=BIGTAB.dmp tables=vihaan.BIGTAB TRANSFORM=TABLE_COMPRESSION_CLAUSE:"ROW STORE COMPRESS ADVANCED"
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "VIHAAN"."BIGTAB"                           70.34 MB 1000000 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER


Job "VIHAAN"."SYS_IMPORT_TABLE_01" successfully completed at Fri Nov 6 08:16:17 2026 elapsed 0 00:00:20




 col SEGMENT_NAME for a20
SELECT segment_name,
       segment_type,
       bytes / 1024 / 1024 AS size_mb,
       bytes / 1024 / 1024 / 1024 AS size_gb
  FROM user_segments
 WHERE segment_name = UPPER('bigtab');

SEGMENT_NAME         SEGMENT_TYPE          SIZE_MB    SIZE_GB
-------------------- ------------------ ---------- ----------
BIGTAB               TABLE                      42 .041015625



TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"ROW\ STORE\ COMPRESS\ ADVANCED\"


 SEGMENT_NAME         SEGMENT_TYPE          SIZE_MB    SIZE_GB
-------------------- ------------------ ---------- ----------
BIGTAB               TABLE                      88   .0859375


SEGMENT_NAME         SEGMENT_TYPE          SIZE_MB    SIZE_GB
-------------------- ------------------ ---------- ----------
BIGTAB               TABLE                      42 .041015625




 SET LINESIZE 150 PAGESIZE 100
COLUMN table_name FORMAT A25
COLUMN compression FORMAT A15
COLUMN compress_for FORMAT A20

SELECT table_name, compression, compress_for FROM dba_tables
WHERE table_name = 'BIGTAB';

TABLE_NAME                COMPRESSION     COMPRESS_FOR
------------------------- --------------- --------------------
BIGTAB                    ENABLED         ADVANCED





Compression Breakdown
Original Size (Uncompressed / Standard): 88 MB (.0859 GB)

Compressed Size (Advanced Compression): 42 MB (.0410 GB)

Space Saved: 46 MB

Reduction Percentage: Approximately 52.27% reduction in storage space.




with para file
 
vi import.par
directory=pdb_exp_dir
dumpfile=BIGTAB.dmp
tables=vihaan.BIGTAB
TRANSFORM=TABLE_COMPRESSION_CLAUSE:"ROW STORE COMPRESS ADVANCED"

impdp vihaan/vihaan123@192.168.1.197/EMPDBREPOS parfile=import.par