Oracle-expdp ora-1555

1. 问题现象

2. 分析处理

2.1 确认导出异常表是否包含lob column坏块 -- 787004.1

-- 1. 创建临时表存储坏块的lob对象
create table corrupt_lobs (corrupt_rowid rowid, err_num number);

-- 2. 查看表大对象字段名称
set lines 168 pages 99
col COLUMN_NAME for a32
col SEGMENT_NAME for a48
select COLUMN_NAME,SEGMENT_NAME from DBA_LOBS
where owner=upper('&&tab_owner') and TABLE_NAME=upper('&&tab_name');

-- 3. 查找corrupted LOBs 并插入临时表
set verify off;

declare
  error_1578 exception;
  error_1555 exception;
  error_22922 exception;
  pragma exception_init(error_1578,-1578);
  pragma exception_init(error_1555,-1555);
  pragma exception_init(error_22922,-22922);
  n number;
begin
  for cursor_lob in (select rowid r, COLUMN_NAME from DBA_LOBS where owner=upper('&&tab_owner') and TABLE_NAME=upper('&&tab_name')) loop
  begin
    n:=dbms_lob.instr(cursor_lob.COLUMN_NAME,hextoraw('889911'));
  exception
    when error_1578 then
      insert into corrupt_lobs values (cursor_lob.r, 1578);
      commit;
    when error_1555 then
      insert into corrupt_lobs values (cursor_lob.r, 1555);
      commit;
    when error_22922 then
      insert into corrupt_lobs values (cursor_lob.r, 22922);
      commit;
    end;
  end loop;
end;
/

-- 4. 将 大字段列设置为空或是排除导出
SQL> update &&tab_owner.&&tab_name set &&lob_column = empty_blob()
     where rowid in (select corrupt_rowid from corrupt_lobs);
SQL> commit;

or

expdp <USER>/<PASSWORD> DIRECTORY=data_pump_dir DUMPFILE=<DUMP_NAME>.dmp LOGFILE=<LOG_NAME>.dmp.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\" 

2.2 检查undo表空间信息和使用情况

show parameter undo_retention
select /*+ rule */ tablespace_name,round(sum(bytes)/1024/1024/1024) size_gb
from dba_data_files
where tablespace_name like '%UNDO%' group by tablespace_name;
select column_name, pctversion, retention
       from dba_lobs
      where table_name = '&&tab_name'
        and owner = '&&tab_owner';
select segment_name,
       tablespace_name,
       r.status,
       (initial_extent / 1024) InitialExtent,
       (next_extent / 1024) NextExtent,
       max_extents,
       v.curext CurExtent
  From dba_rollback_segs r, v$rollstat v
 Where r.segment_id = v.usn(+)
   and segment_name = '&&lob_column';

-- 若需要调整undo retention
alter system set undo_retention=3600 scope=both;
ALTER TABLE &&tab_owner.&&tab_name MODIFY LOB(&&lob_column)(retention);