find top space occupants:
select round (sum(e.bytes/1024/1024/1024)) as gb_size, e.owner, case when e.SEGMENT_NAME like 'SYS_LOB%' then 'lob_of:' || l.table_name ||' colunm:'||l.column_name else e.segment_name end as segment_name from dba_extents e left join dba_lobs l on e.segment_name=l.segment_name group by e.owner, case when e.SEGMENT_NAME like 'SYS_LOB%' then 'lob_of:' || l.table_name ||' colunm:'||l.column_name else e.segment_name end having round (sum(e.bytes/1024/1024/1024)) >10 order by 1 desc; GB_SIZE | OWNER | SEGMENT_NAME ---------- | ------------------------ | ---------------------------------------------------------------------------------------------------- 240 | XXX | lob_of:XXX_DOC colunm:DOC_CONTENT 152 | XXX | lob_of:XXX_REVISION_DOCUMENTS_TMP colunm:FILE_CONTENT 93 | XXX | lob_of:XXX_DOC colunm:ARCHIVED_DOC_CONTENT 63 | XXX | lob_of:XXX_CONTRACTS colunm:FILE_CONTENT 57 | XXX | lob_of:XXX_DOC_TMP colunm:DOC_CONTENT 36 | XXX | lob_of:XXX_CONTRACTS colunm:ARCHIVED_DOC_CONTENT 34 | XXX | lob_of:XXX_REVISION_DOCUMENTS colunm:FILE_CONTENT 33 | XXX | lob_of:XXX_BID_ORG_DOCUMENTS colunm:FILE_CONTENT 33 | XXX | lob_of:XXX_DOC_TMP colunm:ARCHIVED_DOC_CONTENT 32 | XXX | lob_of:XXX_PROTOCOL_DOCUMENTS colunm:FILE_CONTENT 29 | XXX | DR$XXX_DOC_CONT_CTX_I$I 19 | XXX | lob_of:XXX_LOT_PHOTO colunm:PHOTO 18 | XXX | lob_of:XXX_PROTOCOL_DOCUMENTS colunm:ARCHIVED_DOC_CONTENT 17 | XXX | lob_of:XXX_ROSIM_ACTS colunm:DOC_CONTENT 15 | XXX | DR$XXX_DOC_CONT_CTX_I$X 13 | XXX | lob_of:XXX_ROSIM_ACTS colunm:ARCHIVED_DOC_CONTENT 13 | XXX | lob_of:XXX_LOT_PHOTO colunm:ARCHIVED_DOC_CONTENT 11 | XXX | lob_of:XXX_REVISION_DOCUMENTS colunm:ARCHIVED_DOC_CONTENT -------------------------------------------------------
so if we skip blob field at export, we will save 85% of space
database size:
select SUM(BYTES)/1024/1024/1024 from dba_extents SUM(BYTES)/1024/1024/1024 ------------------------- 1017.16553
do export with remap blob field with funtion that return empty blob:
create package\function which return empty blob
create or replace package remap as function null_lob(col blob) return blob; end remap; / create or replace package body remap as function null_lob(col blob) return blob is eblob blob; begin return null; end null_lob; end remap; /
Export:
create directory dump as '+data/dump'; create directory logdir as '/oracle/dump'; expdp \'sys / as sysdba /' schemas=XXX,XXXXXX,XXX_XXX dumpfile=DUMP:exp_xxx.dmp logfile=LOGDIR:exp_xxx.log REMAP_DATA= XXX.FDC_DOC.DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_DOC.ARCHIVED_DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_REVISION_DOCUMENTS_TMP.FILE_CONTENT:SYS.remap.null_lob,XXX.FDC_CONTRACTS.FILE_CONTENT:SYS.remap.null_lob ,XXX.FDC_CONTRACTS.ARCHIVED_DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_DOC_TMP.DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_DOC_TMP.ARCHIVED_DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_REVISION_DOCUMENTS.FILE_CONTENT:SYS.remap.null_lob ,XXX.FDC_REVISION_DOCUMENTS.ARCHIVED_DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_BID_ORG_DOCUMENTS.FILE_CONTENT:SYS.remap.null_lob,XXX.FDC_PROTOCOL_DOCUMENTS.FILE_CONTENT:SYS.remap.null_lob,XXX.FDC_PROTOCOL_DOCUMENTS.ARCHIVED_DOC_CONTENT:SYS.remap.null_lob ,XXX.FDC_ROSIM_ACTS.DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_ROSIM_ACTS.ARCHIVED_DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_LOT_PHOTO.ARCHIVED_DOC_CONTENT:SYS.remap.null_lob,XXX.FDC_LOT_PHOTO.PHOTO:SYS.remap.null_lob
ps: dump size 52gb
found at:
http://dba.stackexchange.com/questions/25540/export-table-without-blob-column