compression Archives - DBACLASS https://dbaclass.com/article-tag/compression/ database administration Mon, 19 Nov 2018 11:44:48 +0000 en-US hourly 1 https://wordpress.org/?v=6.5.5 COMPRESSION in datapump oracle https://dbaclass.com/article/compression-datapump-oracle/ https://dbaclass.com/article/compression-datapump-oracle/#respond Mon, 19 Nov 2018 11:42:17 +0000 http://dbaclass.com/?post_type=article&p=3957 COMPRESSION parameter is used with EXPDP, to compress the generated dump file. NOTE – For using compression parameter with datapump, we need to have Oracle advance compression license.   COMPRESSION Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE. In this below demo, we will compare the dump […]

The post COMPRESSION in datapump oracle appeared first on DBACLASS.

]]> COMPRESSION parameter is used with EXPDP, to compress the generated dump file.

NOTE – For using compression parameter with datapump, we need to have Oracle advance compression license.

 

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

In this below demo, we will compare the dump size with or without compression parameter.

 

Get the DUMPSIZE without COMPRESSION parameter :

cat exp_compress.par

dumpfile=dbaclass_wo_compression.dmp
logfile=dbaclass.log
directory=EXPDIR
tables=DBACLASS.OBJECT_LIST


 expdp parfile=exp_compress.par

Export: Release 12.1.0.2.0 - Production on Mon Nov 19 12:56:14 2018

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=exp_compress.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 29 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DBACLASS"."OBJECT_LIST"                    24.69 MB  219456 rows ---- >>> 25 MB AROUND
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/dbaclass__compression.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 19 12:56:19 2018 elapsed 0 00:00:03

So, without compression, the size is around 25 MB. Let’s do the export with compression.

EXPORT WITH COMPRESSION=ALL

 cat exp_compress.par

dumpfile=dbaclass_compression.dmp
logfile=dbaclass.log
directory=EXPDIR
tables=DBACLASS.OBJECT_LIST
compression=ALL

 expdp parfile=exp_compress.par

Export: Release 12.1.0.2.0 - Production on Mon Nov 19 12:56:48 2018

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=exp_compress.par
Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 29 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DBACLASS"."OBJECT_LIST"                    2.922 MB  219456 rows --- >> 
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/dbaclass_compression.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 19 12:56:56 2018 elapsed 0 00:00:05

We can see, from 25 MB ,it came down to 2 MB :). It really saves a lot of disk space.

However, there is a tradeoff. Use of compression will increase the time required for the export job.

 

W/O COMPRESSION WITH COMPRESSION
SPACE REQUIRED 25mb 3 mb
TIME TAKEN 3 second 5 second
CPU USAGE NORMAL bit more than w/o compression
LICENSE? No additional license Advance compression license required

 

COMPRESSION_ALGORITHM:

We can control the compression ratio using COMPRESSION_ALGORITHM parameter. HIGH compression ratio will use more CPU and also increase export job time.

 

COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

 

PARFILE WILL LOOK AS BELOW:

 cat exp_compress.par

dumpfile=dbaclass_compression.dmp
logfile=dbaclass.log
directory=EXPDIR
tables=DBACLASS.OBJECT_LIST
compression=ALL
COMPRESSION_ALGORITHM=HIGH

The post COMPRESSION in datapump oracle appeared first on DBACLASS.

]]> https://dbaclass.com/article/compression-datapump-oracle/feed/ 0 How to take expdp of a table to multiple directories in oracle https://dbaclass.com/article/how-to-take-expdp-of-a-table-to-multiple-directories-in-oracle/ https://dbaclass.com/article/how-to-take-expdp-of-a-table-to-multiple-directories-in-oracle/#comments Thu, 27 Aug 2015 18:06:22 +0000 http://dbaclass.com/?post_type=article&p=367 Suppose you wish to take a expdp backup of a big table, but you don’t sufficient space in a single mount point to keep the dump. In this case we take expdp dump to multiple directory. DEMO: Create 2 directories: SQL> create directory DIR1 as '/home/oracle/DIR1'; Directory created. SQL> create directory DIR2 as '/home/oracle/DIR2'; Directory […]

The post How to take expdp of a table to multiple directories in oracle appeared first on DBACLASS.

]]> Suppose you wish to take a expdp backup of a big table, but you don’t sufficient space in a single mount point to keep the dump. In this case we take expdp dump to multiple directory.

DEMO:

Create 2 directories:

SQL> create directory DIR1 as '/home/oracle/DIR1';

Directory created.

SQL> create directory DIR2 as '/home/oracle/DIR2';

Directory created.

SQL> grant all on directory DIR1 to public;

Grant succeeded.

SQL> grant all on directory DIR2 to public;

Grant succeeded.

 

Now take export with parallel option:

 

[oracle@localhost ~]$ expdp dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp logfile=test.log directory=DIR1 parallel=2 tables=raj.test

Export: Release 12.1.0.2.0 - Production on Thu Aug 27 10:56:31 2015

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

Username: raj/raj@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "RAJ"."SYS_EXPORT_TABLE_01":  raj/********@orcl dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp logfile=test.log directory=DIR1 parallel=2 tables=raj.test 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
. . exported "RAJ"."TEST"                                10.66 MB   93915 rows
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Master table "RAJ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RAJ.SYS_EXPORT_TABLE_01 is:
  /home/oracle/DIR1/test_01.dmp
  /home/oracle/DIR2/test_01.dmp

Job "RAJ"."SYS_EXPORT_TABLE_01" successfully completed at Thu Aug 27 10:57:09 2015 elapsed 0 00:00:31

 

Now you can see the dump file has been created in multiple directories.

 

Dump file set for RAJ.SYS_EXPORT_TABLE_01 is:
/home/oracle/DIR1/test_01.dmp
/home/oracle/DIR2/test_01.dmp

 

If you wish to compress the size , then you can use compression=all in the expdp command.

 

For import also you can use the similar method.

 

[oracle@localhost ~]$ impdp dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp logfile=test1.log directory=DIR1 parallel=2 tables=raj.test table_exists_action=REPLACE

Import: Release 12.1.0.2.0 - Production on Thu Aug 27 11:28:22 2015

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

Username: raj/raj@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "RAJ"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "RAJ"."SYS_IMPORT_TABLE_01":  raj/********@orcl dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp logfile=test1.log directory=DIR1 parallel=2 tables=raj.test table_exists_action=REPLACE 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RAJ"."TEST"                                10.66 MB   93915 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "RAJ"."SYS_IMPORT_TABLE_01" successfully completed at Thu Aug 27 11:29:05 2015 elapsed 0 00:00:35

 

FYI- To use compression parameter in expdp , we need additional Advanced compression license,( Which is not included in enterprise edition)

The post How to take expdp of a table to multiple directories in oracle appeared first on DBACLASS.

]]> https://dbaclass.com/article/how-to-take-expdp-of-a-table-to-multiple-directories-in-oracle/feed/ 1