a. Analyse FND_LOBS table collect the table size, lob segment size, no of rows in FND_LOBS table, respective LOB Segment associated with FILE_DATA column etc.
b. Make sure you have sufficient space in the Data pump directory before initiating the export of FND_LOBS table.
c. Initiate the export of FND_LOBS table,
$ expdp “‘/ as sysdba'” tables=APPLSYS.FND_LOBS directory=DUMP_FND dumpfile=dev_fndlobs.dmp logfile=dev_fndlobs.log exclude=STATISTICS COMPRESSION=NONE
Excluding the statistics will help the import operation to complete faster. Gather stats can be later run after the import so excluding statistics while exporting is totally fine.
Compression is set to none to mention the expdp not to perform any compressions while exporting the table.
d. Truncate the FND_LOBS after the expdp completes successfully,
SQL> truncate table APPLSYS.FND_LOBS;
e. Shrink the FND_LOBS table once to reclaim any unused space,
SQL> ALTER TABLE APPLSYS.FND_LOBS MODIFY LOB (FILE_DATA) (SHRINK SPACE);
f. Impor the FND_LOBS table,
$ impdp “‘/ as sysdba'” tables=APPLSYS.FND_LOBS directory=DUMP_FND dumpfile=dev_fnd_01.dmp logfile=impdp_fndlobs_2.log table_exists_action=APPEND
Table_Exist_Action is set to append to mention the impdp to just insert the data and not to create the FND_LOBS table.
— Rebuild Index after import
SQL> alter index APPLSYS.FND_NODES_U1 rebuild;
SQL> alter index APPLSYS.FND_NODES_N1 rebuild;
