ISSUE:
ORA-31693: Table data object “APPLSYS”.”FND_LOBS” failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snapshot too old
HOW TO RESOLVE THIS ISSUE?
The block corruption, corrupted rows can be removed by performing expdp following are the sreps to perform expdp on FND_LOBS table
Running Stats
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (‘SYS’);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS;
SQL> EXEC dbms_stats.lock_table_stats (null,’X$KCCLH’);
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (null);
SQL> ALTER SYSTEM SET streams_pool_size=4G SCOPE=BOTH;
SQL> alter system set cursor_sharing=’FORCE’ scope=both;
SQL> alter system set job_queue_processes=0 scope=both;
Disable dbms jobs
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL);
END;
/
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => ‘auto optimizer stats collection’,
operation => NULL,
window_name => NULL);
END;
/
Purge recyclebin
SQL> purge recyclebin;
SQL> purge dba_recyclebin;
Bounce the Databases
Export FND_LOBS
$ expdp “‘/ as sysdba'” tables=APPLSYS.FND_LOBS directory=DUMP_FND dumpfile=dev_fndlobs_%U.dmp logfile=dev_fndlobs_1.log exclude=STATISTICS COMPRESSION=NONE
Truncate FND_LOBS
SQL> truncate table APPLSYS.FND_LOBS
SHRINK FND_LOBS
SQL> ALTER TABLE APPLSYS.FND_LOBS MODIFY LOB (FILE_DATA) (SHRINK SPACE);
SQL> alter index APPLSYS.FND_NODES_U1 rebuild;
SQL> alter index APPLSYS.FND_NODES_N1 rebuild;
Import FND_LOBS
$ impdp “‘/ as sysdba'” tables=APPLSYS.FND_LOBS directory=DUMP_FND dumpfile=dev_fndlobs_01.dmp logfile=impdp_fndlobs_2.log table_exists_action=APPEND
Rebuild Index after import
SQL> alter index APPLSYS.FND_NODES_U1 rebuild;
SQL> alter index APPLSYS.FND_NODES_N1 rebuild
