To check the SEGMENTS APPROCHING MAX EXTENTS

select ‘ALTER ‘||s.segment_type||’ ‘||s.owner||’.’||s.segment_name||’ storage (maxextents unlimited);’ “Alter statement”
from dba_segments s , dba_tablespaces t
where
s.tablespace_name=t.tablespace_name and
t.extent_management<>’LOCAL’ and
s.segment_type in (‘TABLE’,’INDEX’) and
s.owner not in (‘SYS’) and
s.max_extents<>2147483645
order by s.owner,s.segment_type
spool off

Checking for the segment which is approaching max extents (max extents reached)
col segment_name for a30
col owner for a15
set lines 200

select segment_name||’ ‘||segment_type||’ ‘||owner||’ ‘||extents||’ ‘||max_extents||’ ‘||next_extent||’ ‘||pct_increase from
dba_segments where extents=max_extents and tablespace_name=’&tablespace’;

Checking storage parameters of a particular segment
col segment_name for a30
col owner for a15
set lines 200
select segment_name||’ ‘||segment_type||’ ‘||owner||’ ‘||extents||’ ‘||max_extents||’ ‘||next_extent||’ ‘||pct_increase from
dba_segments where segment_name=’WF_LOCAL_ROLES’ and tablespace_name=’APPLSYSD’;

Segments reached maximum extent count in DB

col segment_name for a30
col owner for a15
set lines 200
select tablespace_name,segment_name||’ ‘||segment_type||’ ‘||owner||’ ‘||extents||’ ‘||max_extents||’ ‘||next_extent||’ ‘||pct_increase from
dba_segments where extents=max_extents;

Segments approaching Max extents

select owner, SEGMENT_NAME, segment_type, tablespace_name, NEXT_EXTENT,PCT_INCREASE, INITIAL_EXTENT, EXTENTS,MAX_EXTENTS from dba_segments where tablespace_name=’&tbs’ and 2*extents>max_extents;

Finding out table name

select table_name, column_name from dba_lobs where segment_name = ‘&Segment_name’;
Modifying LOB Segments (EXAMPLES)

alter table <table> modify lob (<lobcolumn>) (storage (maxextents unlimited))

alter table P1CMGT.ASSET modify lob(CONTENT_BINARY) (storage (maxextents unlimited));

alter table APPLSYS.FND_LOBS modify lob(FILE_DATA) (storage (maxextents unlimited));

alter table APPLSYS.FND_LOBS allocate extent;

alter table JTF.JTF_DIAGNOSTIC_LOG modify lob(REPORT) (storage (maxextents unlimited));

alter table JTF.JTF_DIAGNOSTIC_LOG modify lob(REPORT) (storage (maxextents unlimited));

Modifying Normal Extents (EXAMPLES)

alter table GL.GL_DAILY_RATES storage (maxextents unlimited);

alter table GL.GL_DAILY_RATES allocate extent;

alter index APPLSYS.WF_USER_ROLE_ASSIGNMENTS_U1 storage (maxextents unlimited);

alter index APPLSYS.WF_USER_ROLE_ASSIGNMENTS_N1 storage (maxextents unlimited);

alter table APPLSYS.WF_LOCAL_USER_ROLES storage (maxextents unlimited);

alter table APPLSYS.WF_LOCAL_ROLES storage (maxextents unlimited);

alter index CRP.CRP_RESOURCE_PLAN_N1 storage (maxextents unlimited);

alter index CRP.CRP_RESOURCE_PLAN_N2 storage (maxextents unlimited);

alter index CRP.CRP_RESOURCE_PLAN_N3 storage (maxextents unlimited);

Allocating a new extent (EXAMPLES)

alter index CRP.CRP_RESOURCE_PLAN_N1 allocate extent;

alter index CRP.CRP_RESOURCE_PLAN_N2 allocate extent;

alter index CRP.CRP_RESOURCE_PLAN_N3 allocate extent;

alter index CRP.CRP_RESOURCE_PLAN_U1 allocate extent;