How to Estimate Export File Size Without Creating Dump File

PURPOSE

-------

Estimate file size of export dumpfile without creating the actual dump file.

How to estimate export dumpfile size using pipe, /dev/null, and dd

------------------------------------------------------------------

This can be accomplished by using pipe to redirect exp output to dd. At the end of export, dd will report number of blocks written. This is illustrated in following steps.

1) Create a pipe called exp.pipe in /tmp directory

(syntax may differ depending on platform)

% cd /tmp

% mknod exp.pipe p

2) Start reading from exp.pipe with dd, dump output to bit bucket (/dev/null), set blocksize to 1k and execute this process in background

% dd if=/tmp/exp.pipe of=/dev/null bs=1024 &

3) Start the export, setting file=/tmp/exp.pipe

% exp scott/tiger file=/tmp/exp.pipe

4) At the end of exp, look for numbers of records written

Export terminated successfully without warnings.

5+0 records in

5+0 records out

- '5+0 records out' shows 5 records of 1024 bytes were written to the exp dumpfile.

- Step 2 specifies record size(bs) of 1024.

- Size of actual dumpfile would be 1024*5 = 5120 bytes

- Format of 'records out' is f+p, f=full blocks, p=partial block

- For example, if step 4 returns '5+1 records out'

Your actual dumpfile size will be between 5120 bytes(1024*5)

and 6144 bytes(1024*6)

for expdp:

DBMS_SPACE.UNUSED_SPACE will report all free space ABOVE HWM, so is there is lots of free space UNDER
HWM, the actual size is not accurate.

You can use DBMS_SPACE.SPACE_USAGE to get the space information UNDER HWM.

Following is an example:

set serveroutput on

DECLARE
unformatted_blocks NUMBER;
unformatted_bytes NUMBER;
fs1_blocks NUMBER;
fs1_bytes NUMBER;
fs2_blocks NUMBER;
fs2_bytes NUMBER;
fs3_blocks NUMBER;
fs3_bytes NUMBER;
fs4_blocks NUMBER;
fs4_bytes NUMBER;
full_blocks NUMBER;
full_bytes NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE(
segment_owner => 'SCOTT',
segment_name => 'DEPT',
segment_type => 'TABLE',
unformatted_blocks => unformatted_blocks,
unformatted_bytes => unformatted_bytes,
fs1_blocks => fs1_blocks,
fs1_bytes => fs1_bytes,
fs2_blocks => fs2_blocks,
fs2_bytes => fs2_bytes,
fs3_blocks => fs3_blocks,
fs3_bytes => fs3_bytes,
fs4_blocks => fs4_blocks,
fs4_bytes => fs4_bytes,
full_blocks => full_blocks,
full_bytes => full_bytes);

dbms_output.put_line('Space usage report for <OWNER>.<TABLE_NAME>');
dbms_output.put_line('***************************************');
dbms_output.put_line('Unformatted : '||unformatted_blocks||' blocks,'||unformatted_bytes/1024/1024||' MB');
dbms_output.put_line(' 0% < Free Space < 25% : '||fs1_blocks||' blocks,'||fs1_bytes/1024/1024||' MB');
dbms_output.put_line('25% < Free Space < 50% : '||fs2_blocks||' blocks,'||fs2_bytes/1024/1024||' MB');
dbms_output.put_line('50% < Free Space < 75% : '||fs3_blocks||' blocks,'||fs3_bytes/1024/1024||' MB');
dbms_output.put_line('75% < Free Space < 100% : '||fs4_blocks||' blocks,'||fs4_bytes/1024/1024||' MB');
dbms_output.put_line('Full blocks : '||full_blocks||' blocks, '||full_bytes/1024/1024||' MB');
END;
/