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

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           1  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     10485761  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 errorin 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 contiguous 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) ;

# 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;