ORA-1691: unable to extend lobsegment APPLSYS.SYS_LOB in tablespace APPLSYSD

Error:

ORA-1691: unable to extend lobsegment APPLSYS.SYS_LOB0000255030C00040$$ by 1574805 in tablespace APPLSYSD

Analysis & Solution:

Step1: Verify Free space in tablespace & Check data file size


SQL> select dt.tablespace_name,round(sum(df.bytes)/1024/1024/1024,0) "Free_GB"
  from dba_free_space df,dba_tablespaces dt
    where df.tablespace_name=dt.tablespace_name(+)
and df.tablespace_name not in (select tablespace_name from dba_temp_files)
  group by dt.tablespace_name
  order by 1;

TABLESPACE_NAME          Free_GB
------------------------------ ----------
APD                                     0
APPLSYSD                         114
APPLSYSX                         139
APPS_UNDOTBS01                 0
APPS_UNDOTBS02                 6
.

.
ASFD                                    0

SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='APPLSYSD';
 

FILE_NAME                                                    BYTES/1024/1024
------------------------------------------------------------ ---------------
/data01/oracle/crmprdata/applsysd01.dbf                                 9216
/data01/oracle/crmprdata/applsysd02.dbf                                 8192
/data02/oracle/crmprdata/applsysd03.dbf                                 8192
/data02/oracle/crmprdata/applsysd04.dbf                                 9216
/data04/oracle/crmprdata/applsysd05.dbf                                 9692
/data04/oracle/crmprdata/applsysd06.dbf                                 9216
/data02/oracle/crmprdata/applsysd07.dbf                                 8196
/data01/oracle/crmprdata/applsysd08.dbf                                 8696
/data01/oracle/crmprdata/applsysd13.dbf                                 4000
/data04/oracle/crmprdata/applsysd09.dbf                                 8192
.

.

.
27 rows selected.

Free space in tablespace found to be over 100 GB , So it is not space issue but some storage parameter issue

Step 2: If enough free space is available then verify NEXT_EXTENT & MAX_EXTENTS value of the segment highlighted in error

SQL> select * from dba_segments where segment_name='SYS_LOB0000255030C00040$$';

OWNER                          SEGMENT_NAME                  PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_
------------------------------ ----------------------------- ------------------------------ ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -------
APPLSYS                        SYS_LOB0000255030C00040$$                                    LOBSEGMENT         APPLSYSD                               289        84109 25815859200    3149650         32          40960  12900777984           2147483645           50          1               1          289 DEFAULT


SQL> select * from dba_tablespaces where tablespace_name='APPLSYSD';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ --------
APPLSYSD                             8192          40960       40960           1  2147483645           50          0 ONLINE    PERMANENT LOGGING   NO  LOCAL      USER      NO  MANUAL DISABLED



Here, NEXT_EXTENT found to be very huge (12 GB) which is the root cause of this issue.MAX_EXTENTS found OK & marked as unlimited


Step 3:  Modify table lob storage clause to reduce the NEXT_EXTENT size from 12 GB to 1 MB



SQL>  alter table APPLSYS.WF_NOTIFICATION_OUT modify lob ("USER_DATA"."TEXT_LOB") (STORAGE (NEXT 1048576));
 

Table altered.



Step 4:  Verify the modifications made in previous step & check if new extent is allocated


SQL> select * from dba_segments where segment_name='SYS_LOB0000255030C00040$$';

OWNER                          SEGMENT_NAME                 PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_
------------------------------ ----------------------------------------------------------- ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -------
APPLSYS                        SYS_LOB0000255030C00040$$                                   LOBSEGMENT         APPLSYSD                               289        84109 25815859200    3149650         32          40960     1048576           2147483645           50          1               1          289 DEFAULT


Error disappeared & size of the segment started growing

SQL> select bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000255030C00040$$';

BYTES/1024/1024
---------------
     24606.6406

SQL> /

BYTES/1024/1024
---------------
     24607.6563


SQL> select count(1) from dba_extents where segment_name='SYS_LOB0000255030C00040$$';

  COUNT(1)
----------
        33


SQL> select * from dba_extents where segment_name='SYS_LOB0000255030C00040$$';

OWNER        SEGMENT_NAME                   PARTITION_NA SEGMENT_TYPE       TABLESPACE_NAME       EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ------------------------------ ------------ ------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ------------
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                      0        289      84109      40960          5          289
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                      1        406     387084      40960          5          406
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                      2        410     449239      81920         10          410
.

.
.
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     26        429     304259 1132584960     138255          429
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     27        509          9 1698897920     207385          509
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     28        509     726494 2548326400     311075          509
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     29        522          9 3822469120     466610          522
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     30        445     414584 5733703680     699915          445
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     31        526          9 8600535040    1049870          526
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     32        527    1221914    1064960        130          527

33 rows selected.


33rd extend created with 1 MB & No more ORA-1691 error in alert log

To avoid this issue in future proactively you may use below sql or schedule in script..

#Output shows segments with NEXT_EXTENT over 1 GB & relative data file contiguos free space less than 2 GB

set line 900
set pages 200
col SEGMENT_NAME for a25
col FILE_NAME for a50
col TABLESPACE_NAME for a12
col OWNER for a12
select ds.owner,ds.segment_name,ds.segment_type,round(ds.NEXT_EXTENT/1024/1024,0) NEXT_EXTENT_MB ,ds.MAX_EXTENTS,ds.PCT_INCREASE,ds.tablespace_name,dd.FILE_NAME,dd.FILE_ID ,round(dd.bytes/1024/1024,0) SIZE_MB  , round(sum(df.bytes)/1024/1024,0) FREE_MB
from (select segment_name,next_extent,header_file,tablespace_name,segment_type,owner,max_extents,PCT_INCREASE from dba_segments where round(NEXT_EXTENT/1024/1024,0) > 1024  ) ds, dba_data_files dd , dba_free_space df
where ds.header_file=dd.file_id
and dd.file_id=df.file_id
having sum(df.bytes)/1024/1024 - 2048 < round(ds.NEXT_EXTENT/1024/1024,0)
group by ds.owner,ds.segment_name,ds.segment_type,round(ds.NEXT_EXTENT/1024/1024,0) , ds.MAX_EXTENTS ,ds.PCT_INCREASE, ds.tablespace_name,dd.FILE_NAME,dd.FILE_ID ,round(dd.bytes/1024/1024,0) ;



OWNER        SEGMENT_NAME              SEGMENT_TYPE       NEXT_EXTENT_MB MAX_EXTENTS PCT_INCREASE TABLESPACE_N FILE_NAME                                             FILE_ID    SIZE_MB    FREE_MB

------------ ------------------------- ------------------ -------------- ----------- ------------ ------------ -------------------------------------------------- ---------- ---------- ----------

APPLSYS      WF_NOTIFICATION_OUT       TABLE                        1080  2147483645           50 APPLSYSD     /u02/oracle/uatcrmdata/applsysd02.dbf                     289       8192       2626

APPLSYS      SYS_LOB0000255030C00040$$ LOBSEGMENT                   1620         505           50 APPLSYSD     /u02/oracle/uatcrmdata/applsysd02.dbf                     289       8192       2626


# Output shows datafile free space per datafile to verify if space need to be increase or new datafile to be added or NEXT_EXTENT to be reduced

select dd.tablespace_name,dd.FILE_NAME,df.FILE_ID ,round((dd.bytes/1024/1024),0) SIZE_MB  , round(sum(df.bytes)/1024/1024,0) FREE_MB
from dba_data_files dd , dba_free_space df , (select distinct tablespace_name,next_extent from dba_segments where round(NEXT_EXTENT/1024/1024,0) > 1024) ds
where ds.tablespace_name=dd.tablespace_name
and dd.file_id=df.file_id
having sum(df.bytes)/1024/1024 - 2048 < round(ds.NEXT_EXTENT/1024/1024,0)
group by dd.tablespace_name,dd.FILE_NAME,df.FILE_ID ,round((dd.bytes/1024/1024),0) ,round(ds.NEXT_EXTENT/1024/1024,0)
order by 1,5;



 
TABLESPACE_N FILE_NAME                                             FILE_ID    SIZE_MB    FREE_MB

------------ -------------------------------------------------- ---------- ---------- ----------

APPLSYSD     /u02/oracle/uatcrmdata/applsysd04.dbf                     420       7144       2001

APPLSYSD     /u02/oracle/uatcrmdata/applsysd04.dbf                     420       7144       2001

APPLSYSD     /u02/oracle/uatcrmdata/applsysd02.dbf                     289       8192       2626

APPLSYSD     /u02/oracle/uatcrmdata/applsysd02.dbf                     289       8192       2626

APPLSYSD     /u01/oracle/uatcrmdata/applsysd03.dbf                     406       8992       3222

APPLSYSD     /u02/oracle/uatcrmdata/applsysd01.dbf                      21       8196       3613


   


NEXT_EXTENT is greater than 1 GB & Contiguous free space of the datafile in 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 using .. alter table <owner>.<table_name> <modify lob> <(lob column_name)> (STORAGE (NEXT <size>));

Reference:

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

My Popular Posts