ORA-01555: snapshot too old: rollback segment number with name “” too small

 

به هنگام اجرای export data pump روی جداولی که ستونی از جنس LOB دارند  اگر به خطای زیر برخوردید :

ORA-31693: Table data object failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

ORA-01555: snapshot too old: rollback segment number with name “” too small

ORA-22924: snapshot too old Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

در ابتدا مقادیر undo و retention را بررسی کرده و در صورت لزوم آنها را افزایش دهید:

> show parameter undo_%;

NAME TYPE VALUE
————————————————– ———– ——–
undo_management string AUTO
undo_retention integer 16500
undo_tablespace string UNDOTBS1

> select file_name,tablespace_name,trunc(bytes/1024/1024) mb, trunc(maxbytes/1024/1024) mm
FROM dba_data_files
where tablespace_name = ‘UNDOTBS1’;

FILE_NAME TABLESPACE_NAME MB MM
——————————————————————–
C:\APP\ADMIN\ORADATA\PROD\UNDOTBS01.DBF UNDOTBS1 5630 32767

>Size of undo with current undo_retention :
Actual Undo size[MBytes]:5630
UNDO retention[Sec]:16500
Needed Undo Size[MBytes]:909.433359

اگر با افزایش آنها همچنان error گرفتین باید Lob ها بررسی گردد:
:مراحل زیر را انجام دهید
ساخت جدولی که بتوان rowid ها را نگهداری کرد:

create table corrupt_lobs (corrupt_rowid rowid);

اجرای رویه ی بالا جهت پر کردن جدول ساخته شده و یافتن lob هایی که خراب شده اند:

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 TABLENAME) loop
begin
n:=dbms_lob.instr(cursor_lob.json,hextoraw(‘889911’));
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
end;
end loop;
end;
/

PL/SQL procedure successfully completed.
SQL > select * from corrupt_lobs;

CORRUPT_ROWID
——————
AAAL9HAAPAAAezUAAE

جهت تست اینکه همین rowid باعث بوجود آمدن خطای مذکور می گردد دستورات زیر را اجرا کنید تا با خطای مجدد روبرو شوید:

$ expdp  dumpfile=TS.dmp logfile=TS.log tables=TABLENAME query=\”where rowid = \’AAAL9HAAPAAAezUAAE\’\”

 جهت حذف LOB های خراب دستور زیر را اجرا کنید:

update TABLENAME set COLUMNNAME= empty_clob()
where rowid in (select corrupt_rowid from corrupt_lobs);

موفق باشین

محسن صفابخش

IN ENGLISH

During execution of expdp facing following issue (Oracle 11g in linux enviornment)

ORA-31693: Table data object failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

ORA-01555: snapshot too old: rollback segment number with name “” too small

ORA-22924: snapshot too old Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

1-At first increase your undo tablespace and/or retain undo for longer and/or guarantee it is retained

> show parameter undo_%;

NAME TYPE VALUE
————————————————– ———– ——–
undo_management string AUTO
undo_retention integer 16500
undo_tablespace string UNDOTBS1

> select file_name,tablespace_name,trunc(bytes/1024/1024) mb, trunc(maxbytes/1024/1024) mm
FROM dba_data_files
where tablespace_name = ‘UNDOTBS1’;

FILE_NAME TABLESPACE_NAME MB MM
——————————————————————–
C:\APP\ADMIN\ORADATA\PROD\UNDOTBS01.DBF UNDOTBS1 5630 32767

>Size of undo with current undo_retention :
Actual Undo size[MBytes]:5630
UNDO retention[Sec]:16500
Needed Undo Size[MBytes]:909.433359

If you get that error again DO:

2-Create table for keeping rowid’s

create table corrupt_lobs (corrupt_rowid rowid);

3-Run blow proc to fill the table:

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 TABLENAME) loop
begin
n:=dbms_lob.instr(cursor_lob.json,hextoraw(‘889911’));
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
end;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL > select * from corrupt_lobs;

CORRUPT_ROWID
——————
AAAL9HAAPAAAezUAAE

 4-Now execute EXPDP with rowid for sure:

$ expdp  dumpfile=TS.dmp logfile=TS.log tables=TABLENAME query=\”where rowid = \’AAAL9HAAPAAAezUAAE\’\”

5-you get that error again and you have to delete currept lOB’s

update TABLENAME set COLUMNNAME= empty_clob()
where rowid in (select corrupt_rowid from corrupt_lobs);

Good Luck

Safabakhsh Mohsen

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *