garden2.jpg

Table Storage Parameter MAXEXTENTS


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 set the maxextents to a value passed to this procedure as an argument(a_max) for all tables and all schemas where a_max > total number of extents for that segment. except for tables belonging to (‘SYS’,'SYSTEM’).

Author: idbasolutions.com

Run this proceudre from SQL prompt:

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

exec p_seg_set_maxext_table (2147483645) will set MAXEXTENTS to unlimited.

<strong>The code:</strong>

create or replace procedure

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

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

and SEGMENT_TYPE=’TABLE’

and MAX_EXTENTS < to_number(a_max)

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(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_table;

Latest Blog Items

email

Thank you for your interest in idbasolutions.

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