Difference in database dump file size

dumporacleoracle-11g

Actually I am bit confused with my database export, while i exporting it shows "Total estimation using BLOCKS method: 20.83 GB" But my DB export dump is only 6 GB. Few days back we deleted some unwanted files and data from our database. But I am not getting idea about this huge difference. Also, my export dump file is not showing any kind of error messages in the log files.

After importing this dump on an server and check DBA segemt using below cmd 

"SELECT OWNER, SUM(BYTES)/1024/1024 SIZE_MB FROM DBA_SEGMENTS
group  by owner; "

data seems to be fine but the size shows a large difference.

Please advice if any body have idea on this?

Best Answer

Data pump uses BLOCKS method to estimate the size of the dump by default during the export. The estimate is calculated by multiplying the number of database blocks used by the source objects, multiply the block sizes.

If the tables are fragmented then actual size may vary.

In your case, as you have said, you had heavy deletion in the past which didn't shrink down the High Water Mark leading data pump to count empty blocks as well during the estimation.

Dump file only contains index DDLs during the export and these DDLs runs when we import it resulting in bigger database size.

Demonstration:

For demonstration I have created one table selecting from all_objects view. 

SQL> create table test_tbl as select * from sys.all_objects;

Table created.

The newly created table has 1664 blocks.((1664*8)/1024=13). Which is 13MB.

SQL> select blocks from user_segments where segment_name='TEST_TBL';

    BLOCKS
----------
      1664

Now lets estimate the dump size of this table.

[oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:16:20 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                               13 MB
Total estimation using BLOCKS method: 13 MB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:16:28 2017 elapsed 0 00:00:05

Its exactly 13MB.

Next, lets delete all the records from the table and try estimate the dump size of table.

SQL> delete from test_tbl;

89517 rows deleted.
SQL> commit;

Commit complete.

Lets run expdp-

oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:24:52 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                               13 MB
Total estimation using BLOCKS method: 13 MB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:25:03 2017 elapsed 0 00:00:09

Its still 13 MB.

So such fragmentation may exist in tables that we try to export and the estimated size can be misleading.

Better way to estimate size of the dump file would be using ESTIMATE=STATISTICS in expdp command. But you should gather table statistics to gain accuracy as shown below.

SQL> exec dbms_stats.gather_table_stats('JAY','TEST_TBL');

PL/SQL procedure successfully completed.

Again, lets try to estimate the dump size.

[oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y estimate=statistics

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:45:02 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y estimate=statistics 
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                            11.43 KB
Total estimation using STATISTICS method: 11.43 KB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:45:07 2017 elapsed 0 00:00:03

Now it's showing accurate result, its 11.43 KB which contains meta data only.

References:

  • Data Pump Export
  • DBMS_STATS

    • Related Question