Table storage parameter MAXEXTENTS
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 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: Bobby Akbari at 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;
/