Using Datapump its a very easy task but want to check whether we can estimate the size of the Compressed Export Dump file.
From 11g onwards, advanced compresssion was introduced which will compress DATA's also when compared to the option of compressing only METADATA which was available in 10g.
##########################
# Task ##########################
Lets see the Difference between using Normal & Compressed
##########################
# Normal Export Estimation
##########################
Command to Estimate export dumpfile.
expdp / tables=u1.T1393 estimate_only=y
mydb_2 @ myserver:/dcunix
> expdp \'/ as sysdba\' tables=u1.T1393 estimate_only=y
Export: Release 11.2.0.3.0 - Production on Fri Mar 1 02:32:30 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=u1.T1393 estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "u1"."T1393" 190.4 GB
Total estimation using BLOCKS method: 190.4 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 02:33:06
So Estimated Size using Normal One is 190.4 GB.
##########################
# Compressed Export Estimation
##########################
Command to Estimate Compressed export dumpfile.
expdp / tables=u1.T1393 tables=u1.T1393 compression=all estimate_only=y
mydb_2 @ myserver:/dcunix
> expdp \'/ as sysdba\' tables=u1.T1393 compression=all estimate_only=y
Export: Release 11.2.0.3.0 - Production on Fri Mar 1 02:34:29 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=u1.T1393 compression=all estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "u1"."T1393" 190.4 GB
Total estimation using BLOCKS method: 190.4 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 02:34:40
So Estimated Size using Compressed One is also 190.4 GB.
##########################
# End of Story
##########################
We cant estimate the Size of the compressed Export Dump File. Hope Oracle May introduce this in Upcoming Versions.
In 10g, we can compress only METADATA's but from 11g onwards we can compress DATA's also.
Available Options in 11g, Compression Parameters are,NONE
METADATA_ONLY
DATA_ONLY
ALL
This Advanced Compression in 11g requires license so make sure before using it.
6 comments:
Hi Sri,
I am just trying to export one table FBTB_TXNLOG_DETAILS_HIST. By this "Total estimation using BLOCKS method: 176.1 GB" does it mean that the dump file is going to be 176.1 GB..?
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "APPDBA"."SYS_EXPORT_SCHEMA_01": appdba/******** parfile=expdp_FBTB_TXNLOG_DETAILS_HIST_11122013.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 176.1 GB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Maximum it can be of 176.1 GB. But mostly when we take export it comes less than that.
And in case if the table has fragmentation, then the dump file will be of less size but datapump estimate option will show 176.1 GB only. we cannot see the actual size without fragmentation.
Thanks Sri for this valuable post. It really helped me..
You are Welcome Nithya
I have LOB data which is around 160.4GB the expdp taking 16 long hours to complete. Please suggest..
11gR2 on Oracle Linux 6
expdp \"SYS/abcvc@orcl AS SYSDBA\" dumpfile=datapump:$dumpfile TABLES=schema.ATTACHMENT
Hi Habeeb,
I don't find we can improve the performance of a LOB export. (Even Parallel option will not work).
But you can use below workaround if its applicable.
1) Sometimes we do refresh of production with test environment and we take full export of schema/database. But actually, test environment doesn't need that LOB data's. So if this is the case then you can exclude that columns alone from those tables which will help.
2) export using transportable tablespace option speeds up the operation of taking export. ( I have performed it and it was almost half the time it took from normal datapump export.)
Kindly check if any of the above option is feasible...
Post a Comment