garden1.jpg

Index max extents

 


 

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 max extents:

This script set the maxextents to a value passed to this procedure as

an argument(a_max) for all indexes and all schemas where a_max > total number

of extents for that segment.

except for tables belonging to (’SYS’,’SYSTEM’)

p_seg_set_maxext_indx

idbasolutions.com

 

Run this proceudre from SQL prompt:

EX: exec p_seg_set_maxext_indx (500) will set MAXEXTENTS to 500

exec p_seg_set_maxext_indx (2147483645) will set MAXEXTENTS to unlimited.

The code:

create or replace procedure

p_seg_set_maxext_indx (a_max IN dba_segments.MAX_EXTENTS%type)

as

v_cursorid integer;

status integer;

cursor c_dba_segments is

select OWNER, SEGMENT_NAME,SEGMENT_TYPE

from dba_segments s

where OWNER not in (’SYS’,’SYSTEM’)

and SEGMENT_TYPE=’INDEX’

and MAX_EXTENTS < to_number(a_max)

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(MAXEXTENTS ‘ || to_number(a_max) || ‘ ) ‘,

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 ||

‘ MAXEXTENTS set to ‘|| a_max);

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_maxext_indx;

 

 

 

Latest Blog Items

email

Thank you for your interest in idbasolutions.

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