garden3.jpg

Table Storage Parameter NEXT


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;

Latest Blog Items

email

Thank you for your interest in idbasolutions.

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