garden1.jpg

Lookup and investigate Oracle objects LOBSEGMENT.



Oracle lobsegment

Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.
1) Find LOB Segment detail.

select l.owner,l.table_name,s.segment_name,l.COLUMN_NAME,
s.segment_type,s.bytes,s.extents,round(s.next_extent/1048576) NEXT_EXTENT_MB,
s.max_extents MAX
from dba_segments s, dba_lobs l
where s.segment_type like ‘LOB%’
and s.segment_name=l.segment_name
and s.segment_name= upper(‘&LOB_NAME’);

2) LOB Segment associated table storage definition.

select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,INITIAL_EXTENT,
round(NEXT_EXTENT/1048576) NEXT_EXTENT_MB,MAX_EXTENTS,sum(EXTENTS),round(sum(BYTES)/1048576) SIZE_MB
from dba_segments
where OWNER = upper(‘&OWNER’)
and SEGMENT_NAME = upper(‘&TABLE_NAME’)
group by SEGMENT_NAME,SEGMENT_TYPE, TABLESPACE_NAME, INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS;

3) LOB Segment storage definition.

select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,INITIAL_EXTENT,
round(NEXT_EXTENT/1048576) NEXT_EXTENT_MB,MAX_EXTENTS,sum(EXTENTS),round(sum(BYTES)/1048576) SIZE_MB
from dba_segments
where OWNER = upper(‘&OWNER’)
and SEGMENT_NAME = upper(‘&LOB_NAME’);
group by SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS;

4) How to alter LOB storage definition.

You may use the following to alter the table and the associated LOB allowing for more extents:

alter table owner.table_name
modify LOB(lob_column_name)
(storage (next 100M MAXEXTENTS 1000));

Example:
User is getting the following error:

APP-FND-00565: The export failed in APP_EXPORT.DUMP_DATA
PL/SQL ERROR: ORA-01691: unable to extend lob segment APPLSYS.SYS_LOB0000033586C00004$$ by 311075 in tablespace APPLSYSD

Looking at tablespace APPLSYSD I can see that it does have room to extent at datafile level for another 800 MB. And TEMP has plenty of room as well.

Using the SQL provided in this page: Looking at the LOB:
SYS_LOB0000033586C00004$$

It looks like Column FILE_DATA of datatype LOBSEGMENT in table APPLSYS.FND_LOBS has grown to be 5 GB in size and was trying to extent itself grabbing another 2.3 GB (NEXT_EXTENT is 2548301824) which of course it could not do, I modified it so it can extent only by another 100 MB.

SQL> alter table APPLSYS.FND_LOBS modify lob (FILE_DATA)
(storage (maxextents 1000 next 100M));

Table altered.

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com