Thursday, January 8, 2009

Why there is a diff btwen DBA_SEGMENTS & DATAPUMP of particular schema size

Why there is a difference between DBA_SEGMENTS & DataPUMP of size of particular schema
======================================================================================

1. SELECT SUM(bytes)/1024/1024/1024 AS size_gig FROM DBA_SEGMENTS WHERE OWNER= 'XXX';

2. $ expdp estimate_only=y schemas=xxx

In Common, data pump export would show less value than the size computed from dba_segments/extents


ESTIMATE_ONLY - Use this parameter to estimate the required export file size without starting an actual export job.
ESTIMATE - The ESTIMATE parameter will tell you how much space your new export job is going to consume.
Estimate_only shows the export dump file size not a schema size.

Also, DBA_SEGMENTS contains INDEX segments too,

So, The following query may give you close result

select sum(bytes)/1024/1024/1024 as size_gig, segment_type
from dba_segments
where owner='ENDUR01'
group by segment_type;

No comments: