How To Estimate Disk Space Needed for an Export Job ?

-

If we  just  want to  know how  much  disk space  for  the  dump  without  actually   exporting  any data, we  can use the ESTIMATE_ONLY=y parameter on  the  expdp  command. ESTIMATE_ONLY=y  parameter  estimate  the space in bytes per tables. The below demo will show that how a system user wants to see the disk space estimates on  scott schemas export. 


[oracle@primaryDB ~]$ expdp schemas=scott estimate_only=y directory=DATA_PUMP_DIR

Export: Release 11.2.0.4.0 - Production on Sat Sep 14 09:10:16 2019

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_02":  /******** AS SYSDBA schemas=scott estimate_only=y directory=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "SCOTT"."EMP2"                                104 MB
.  estimated "SCOTT"."DEPT"                                 64 KB
.  estimated "SCOTT"."EMP"                                  64 KB
.  estimated "SCOTT"."EMP1"                                 64 KB
.  estimated "SCOTT"."EMPLOYEE"                             64 KB
.  estimated "SCOTT"."PHANI"                                64 KB
.  estimated "SCOTT"."SALGRADE"                             64 KB
.  estimated "SCOTT"."STACK"                                64 KB
.  estimated "SCOTT"."BONUS"                                 0 KB
Total estimation using BLOCKS method: 104.4 MB
Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Sat Sep 14 09:10:42 2019 elapsed 0 00:00:15


Note : 

  • The ESTIMATE_ONLY parameter cannot be used in conjunction with the QUERY parameter dumpfile.
  • Estimate Parameter  do not give the accurate size . It gives the approax.  size of the dumpfile . 
  • If the Data Pump export job involves compressed tables, the default size estimation given for the compressed table is inaccurate when ESTIMATE=BLOCKS is used. This is because the size estimate does not reflect that the data was stored in a compressed form. To get a more accurate size estimate for compressed tables, use ESTIMATE=STATISTICS.
  • The estimate may also be inaccurate if the QUERY, SAMPLE, or REMAP_DATA parameter is used.

Now we see that we need  104MB  disk space to export the entire data base.

This approach may not give you exact size since it uses block method, you can use statistics method which would be more accurate no.

Refer to ESTIMATE={BLOCKS | STATISTICS}

It's all depends on the condition's . Both parameters didn't give the exact size but in general cases block size gives the approax size .

I have done some experiment and come to conclusion that Actual dump size may varies upto 50% of original size , If size of the dumpfile is small then there may be more deviation in size but in case of large file , deviation keeps on decreasing .

I have added few notes on this .

You comments are very useful for me .

Thanks and good time ahead . :) :)



Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more