ORA-31693: Table data object failed to load/unload
ORA-31693: Table data object failed to load/unload
ORA-31693: Table data object
"<SCHEMA_NAME>"."<TABLE_NAME>" failed to
load/unload (LOB Table)
The export backup (expdp) are logical database
backups that extract logical definitions and data from the database to a file.
Export backups are cross-platform and can be easily moved from one operating
system to the other.
When exporting a database backup using Data Pump
(expdp), and all is exporting fine, except for one table it fails to export
with the following errors:
ORA-31693: Table data object
"<SCHEMA_NAME>"."<TABLE_NAME>" 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 154 with
name "<ROLLBACK_SEGMENT_NAME>" too small
Here Table is LOB table, Check the LOB corruption. if
no corruption is found in the LOB then may be the LOB Retention is not defined
properly.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------
----------- ---------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
16335
SQL> select retention from dba_lobs where owner='<SCHEMA_NAME>' and table_name='<TABLE_NAME>';
RETENTION
----------
900
Here the retention comes back showing 900 seconds (15 minutes)
which is the same as the current UNDO_RETENTION but with a maximum length of
16335 seconds.
o Modify the current UNDO_RETENTION for the database:
SQL>ALTER SYSTEM SET UNDO_RETENTION = 16500 scope=both sid='*';
o Modify the LOB retention to become greater than the undersized
retention parameter:
SQL> alter table <SCHEMA_NAME>.<TABLE_NAME> modify lob(<LOB_COLUMN_NAME>) (pctversion 5);
Table altered.
SQL> alter table <SCHEMA_NAME>.<TABLE_NAME> modify lob(<LOB_COLUMN_NAME>) (retention);
Table altered.
o Query the lob retention again to verify that the change has
taken hold
SQL> select retention from dba_lobs where owner='<SCHEMA_NAME>' and table_name='<TABLE_NAME>';
RETENTION
----------
16500
o Now Perform the export again.
Note :- Here Check the LOB corruption and Resolve it if Yes
Run the below
script against the LOB tables in order to check for corruption:-
set serverout on
exec dbms_output.enable(100000);
declare
page
number;
len number;
c varchar2(10);
charpp number := 8132/2;
begin
for r in (select rowid rid, dbms_lob.getlength (<your_clob_column>) len
from <your_table_with_clcob_column>) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (<your_clob_column>, 1, 1+ (page * charpp))
into c
from <your_table_with_clcob_column>
where rowid = r.rid;
exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/
If no error,
LOB is OK.
Else if error (i.e. ORA-1403,
ORA-1578, ORA-1555, ORA-22922) happens that means LOB has been corrupted.
To solve the issue:
Some more articles you
might also be interested in :-
Distributed transaction in
oracle
ORA-02050: transaction
rolled back, some remote DBs may be in-doubt
ORA-01591: lock held by
in-doubt distributed transaction
ORA-31693: Table data object
failed to load/unload in expdp
Comments
Post a Comment