Monday, September 26, 2016

R12 Error : ORA-1691: unable to extend lobsegment

Problem

Following error occurred while attached and save the attachment on oracle forms in R12.
ORA-1691: unable to extend lobsegment APPLSYS.SYS_LOB0000033088C00004$$ by 2362205 in tablespace APPLSYSD

Solution

Check if there enough free space in tablespaces. if so please follow the steps

1. Verify NEXT_EXTENT & MAX_EXTENTS value of the segment highlighted in error
SQL> select BYTES,EXTENTS,NEXT_EXTENT,MAX_EXTENTS from dba_segments where segment_name='SYS_LOB0000033088C00004$$';

     BYTES    EXTENTS NEXT_EXTENT MAX_EXTENTS
---------- ---------- ----------- -----------
3.8703E+10         33  1.9351E+10         505
NEXT_EXTENT found to be very huge (19 GB) which is the root cause of this issue

2.Reduce segment NEXT_EXTENT size from 19 GB to 1 MB
SQL> select owner, table_name, column_name, SEGMENT_NAME  from dba_lobs where segment_name='SYS_LOB0000033088C00004$$';

OWNER          TABLE_NAME       COLUMN_NAME       SEGMENT_NAME
-----------------------------------------------------------------------------
APPLSYS        FND_LOBS          FILE_DATA      SYS_LOB0000033088C00004$$
SQL> alter table applsys.FND_LOBS modify lob ("FILE_DATA") (STORAGE (NEXT 1048576));

Table altered.
SQL> select BYTES,EXTENTS,NEXT_EXTENT,MAX_EXTENTS from dba_segments where segment_name='SYS_LOB0000033088C00004$$';

     BYTES    EXTENTS NEXT_EXTENT MAX_EXTENTS
---------- ---------- ----------- -----------
3.8703E+10         33     1048576         505

NEXT_EXTENT is greater than 1 GB & Contiguous free space of the datafile which segment is located may not accommodate next few extents which will lead to allocation of next extent in new datafile. Please verify if other datafiles in the same tablespace has sufficient contiguous space available . If not then add a datafile with size equal to or greater than NEXT_EXTENT else reduce NEXT_EXTENT size

Reference : 
Ora-1691: Unable To Extend Lobsegment Applsys.Sys_lob0000033489c00004$$ By 92170 (Doc ID 378377.1)

No comments:

Post a Comment