drop table t_lob purge;
drop tablespace test_lob including contents and datafiles;
create tablespace test_lob datafile size 4m;
create table t_lob(a number,b clob)
lob(b) store as (
tablespace test_lob
chunk 8k
PCTVERSION 2
disable storage in row);
begin
for i in 1..20 loop
insert into t_lob values(i,'this is '||i);
commit;
end loop;
end;
/
ALTER TABLE t_lob MODIFY LOB (b) ( PCTVERSION 1 );
begin
for i in 1..10 loop
update t_lob set b = i||b;
commit;
end loop;
end ;
/
这时候查询sql会出现案例中类似的报错
1 2 3 4
SQL> select * from t_lob as of timestamp timestamp'2019-09-19 16:31:00'; ERROR: ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old
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, <LOB_COLUMN> from <TABLE_NAME>) loop begin n:=dbms_lob.instr(cursor_lob.<LOB_COLUMN>,hextoraw('889911')); exception when error_1578 then insertinto corrupt_lobs values (cursor_lob.r, 1578); commit; when error_1555 then insertinto corrupt_lobs values (cursor_lob.r, 1555); commit; when error_22922 then insertinto corrupt_lobs values (cursor_lob.r, 22922); commit; end; endloop; end; /
然后通过上面查到的rowid,对这些lob字段数据进行清空,最后成功导出
1 2 3 4 5 6 7 8
SQL> update <TABLE_NAME> set <LOB_COLUMN> = empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);
( for BLOB and BFILE columns use EMPTY_BLOB; for CLOB and NCLOB columns use EMPTY_CLOB )
-- Or export the table without the corrupted row, like:
% expdp system/<PASSWORD> DIRECTORY=my_dir DUMPFILE=<dump_name>.dmp LOGFILE=<logfile_name>.logTABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY=\"WHERE rowid NOT IN \(\'>corrupt_rowid>\'\)\"