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