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 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 1048576 1 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) ;
# 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;
Reference:
Ora-1691: Unable To Extend Lobsegment Applsys.Sys_lob0000033489c00004$$ By 92170 (Doc ID 378377.1)
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 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 1048576 1 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)
good
ReplyDelete