Table storage parameter NEXT
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-01553: MAXEXTENTS must be no smaller than
ORA-02221: Invalid MAXEXTENTS storage option value
Quick overview:
This script will set the next_extent to a value passed as an argument (a_next_extent) for all tables for all schemas.
Excluding objects belonging to (‘SYS’,'SYSTEM’)
A 10MB next_extent should be ok for most environment. This procedure will not modify the next_extent of a table 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 loads 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.
Run this proceudre from SQL prompt:
EX: exec p_seg_set_nextext_table (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_table (10485760);
This sets NEXT set to 10MB
The code:
create or replace procedure
p_seg_set_nextext_table (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
where OWNER not in (‘SYS’,'SYSTEM’)
and SEGMENT_TYPE=’TABLE’
and NEXT_EXTENT < to_number(a_next_extent)
and SEGMENT_NAME not like ‘SYS_IOT_OVER_%’;
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_table;
/