How to increase Index storage parameter NEXT in Oracle.
idbasolutions.com September 9, 2004
Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.
Related ORA errors:
If you get the following errors then this script, if used appropriately, would help you resolve ORA- errors such as the following:
ORA-03232: Unable to allocate an extent of
ORA-1652: Unable to extent TEMP
ORA-01654: Unable to extend index
ORA-01632: Max extents reached in index
ORA-01553: MAXEXTENTS must be no smaller than
ORA-02221: Invalid MAXEXTENTS storage option value
Increase all database indexes next extent:
This script will set the next_extent to a value passed as an argument (a_next_extent) for all indexes for all schemas. For indexes it will exclude primary key indexes as storage parameters are not allowed.
Excluding objects belonging to (’SYS’,’SYSTEM’).
10MB next_extent should be ok for most environment. This procedure will not modify the next_extent of an index if the next_extent is already greater than the a_next_extent.
Excluding objects like ‘SYS_IOT_OVER_%’ due to what seems to be a bug: Bug 1432150, which
causes an ORA-25191: cannot reference overflow table of an index-organized table.
Best practice when working within a locally managed tablespaces:
Ttablespaces such as follow:
create tablespace DATA1
datafile ‘/u03/oradata/develdata/data01.dbf’ size 2000M
Extent management local autoallocate
Segment space management manual;
Then on tables and indexes do as follow:
Alter table table_name storage (amxextents 1010 next 10485760);
Here what will happen is that if the table is pretty small and will undergo data loading then the 5 or 6 few extents will be very small, but as the load goes Oracle will make the next extent to be 10MB as specified in the storage clause, this is true only for Extent management local autoallocate and not for extent management local uniform size.
Author: Babak Akbari at idbasolutions.com
Run this proceudre from SQL prompt:
EX: exec p_seg_set_nextext_indx (10485760)
This will set NEXT_EXTENT to 10MB
This was tested on a 11.5.9 Apps instance running on a 9.2.0.4 database and run for 20 minutes for segment type TABLE. And close to 30 minutes for segment type INDEX.
exec p_seg_set_nextext_indx (10485760) ;
This sets NEXT set to 10MB.
The code:
create or replace procedure
p_seg_set_nextext_indx (a_next_extent IN dba_segments.next_extent%type)
as
v_cursorid integer;
status integer;
cursor c_dba_segments is
select OWNER, SEGMENT_NAME,SEGMENT_TYPE,NEXT_EXTENT
from dba_segments s
where OWNER not in (’SYS’,’SYSTEM’)
and SEGMENT_TYPE=’INDEX’
and NEXT_EXTENT < to_number(a_next_extent)
and SEGMENT_NAME not like ‘SYS_IOT_OVER_%’
and not exists (select 1
from dba_constraints c
where CONSTRAINT_TYPE =’P’
and c.INDEX_NAME=s.SEGMENT_NAME
and c.INDEX_OWNER=s.OWNER);
v_dba_segments c_dba_segments%rowtype;
begin
open c_dba_segments;
v_cursorid:=dbms_sql.open_cursor;
fetch c_dba_segments into v_dba_segments;
while ( c_dba_segments%found ) loop
dbms_sql.parse(v_cursorid,
‘ALTER ‘||v_dba_segments.segment_type||’ ‘||v_dba_segments.owner||’.’||
v_dba_segments.segment_name||
‘ storage(NEXT ‘ || to_number(a_next_extent) || ‘ ) ‘,
dbms_sql.native);
status:=dbms_sql.execute(v_cursorid);
dbms_output.put_line(’Segment: ‘|| v_dba_segments.segment_type|| ‘ ‘||
v_dba_segments.owner||’.’||v_dba_segments.segment_name ||
‘ NEXT EXTENT set to ‘ || a_next_extent);
fetch c_dba_segments into v_dba_segments;
end loop;
close c_dba_segments;
dbms_sql.close_cursor(v_cursorid);
exception
when others then
dbms_output.put_line(’Error…… ‘);
dbms_sql.close_cursor(v_cursorid);
raise;
end p_seg_set_nextext_indx;
/