Skip to content

oracle export data without blob

oracle export data without blob published on Комментариев к записи oracle export data without blob нет

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Primary Sidebar