a. Find the Size of the FND_LOBS table and its associated lob_segment size.
SQL> select owner, table_name, column_name, segment_name, tablespace_name from dba_lobs where table_name=’FND_LOBS’;
SQL> select a.owner, a.object_name, a.object_type, a.created, b.tablespace_name,sum(b.bytes/1024/1024)
from dba_objects a, dba_segments b
where a.object_name=b.segment_name
and a.object_name = ‘&segment_name_from_previousquery’
group by a.owner, a.object_name, a.object_type, a.created, b.tablespace_name
order by 6 desc;
Get the segment_name, tablespace_name, lob_segment name from the above queries.
Note : – Alter table Move shall be performed in two ways,
1. Move the table within the tablespace.
More space needs to be added to the tablespace and then later can be resized after the table is successfully moved.
2. Move the table to another tablespace and move it back to its original tablespace.
A new tablespace with the same size shall be created and later dropped once the table is successfully moved. Using this method the table needs to be moved twice. (Source tablespace to new tablespace and from new tablespace to its original source tablespace)
Alter table Move can be performed online, but performing the move table when EBS application online will be time consuming. It is better to bring down the EBS services for faster completion and avoid any errors.
b. Add sufficient space to the tablespace of FND_LOBS for the move task, for example if your FND_LOBS lob_segement size is 500 GB add datafiles of 500GB.If you want to create a new tablespace for this, then create a new tablespace with the same size of the FND_LOBS and its LOB_SEGMENT.
c. Perform FND_LOBS move.
SQL> alter table applsys.fnd_lobs move;
(or)
SQL> alter table applsys.fnd_lobs move tablespace target_tablespace.
d. Move the respective LOB_SEGMENT of FND_LOBS,
SQL> alter table APPLSYS.FND_LOBS move lob (FILE_DATA) store as segment_name_from_previousquery;
(or)
SQL> alter table APPLSYS.FND_LOBS move LOB (FILE_DATA) store as segment_name_from_previousquery tablespace target_tablespace;
After the FND_LOBS and its LOB Segment is moved to another tablespace, move it back to its original source tablespace.
SQL> alter table applsys.fnd_lobs move tablespace source_tablespace.
SQL> alter table APPLSYS.FND_LOBS move LOB (FILE_DATA) store as segment_name_from_previousquery tablespace source_tablespace;
c. Rebuild the indexes of FND_LOBS,
SQL> select owner, index_name, status from dba_indexes where table_owner=’APPLSYS’ and table_name=’FND_LOBS’;
SQL> alter index APPLSYS.FND_NODES_U1 rebuild;
SQL> alter index APPLSYS.FND_NODES_N1 rebuild;
The LOB_SEGMENT index will be rebuild automatically as part of move table.
