How to resolve ORA errors such as ora-03232, ORA-1652, ORA-01654, ORA-01632, ORA-01553 and ORA-02221
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
idbasolutions.com September 9, 2004
Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.
Quick overview:
This script sets the default storage for all tablespaces for next and
maxextents to a value passed to this procedure as an argument(a_next a_maxextents) for all tablespaces except TEMPORARY and UNDO tablespace.
This needs to be done so new objects created will not run out of extents in case the default tablespace storage is set too low.
Extent management local autoallocate:
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.
Extent management local uniform:
This would work even in locally managed tablespaces with uniform sized where let’s say
uniform size is set to 1 MB and you set the next to 10 MB. Then all new objects or
existing objects will have next set to 10 MB and will grow by grabing a 10 MB extent and not 1 MB (I did verify this fact using Quest space manager and looked the the object size and number of extents and their next extent setting).
The code:
Run this proceudre from SQL prompt:
EX:
exec p_tbs_default_maxext_next (10485760,2147483645)
This will set NEXT to 10M and MAXEXTENTS to unlimited.
Following is the code to create the procedure. Logon as sysdba:
create or replace procedure
p_tbs_default_maxext_next (a_next IN dba_tablespaces.NEXT_EXTENT%type,
a_maxextents IN dba_tablespaces.MAX_EXTENTS%type)
as
v_cursorid integer;
status integer;
cursor c_dba_tablespaces is
select TABLESPACE_NAME
from dba_tablespaces
where CONTENTS not in (’TEMPORARY’,'UNDO’)
and status=’ONLINE’
and ALLOCATION_TYPE=’USER’;
v_dba_tablespaces c_dba_tablespaces%rowtype;
begin
open c_dba_tablespaces;
v_cursorid:=dbms_sql.open_cursor;
fetch c_dba_tablespaces into v_dba_tablespaces;
while ( c_dba_tablespaces%found ) loop
dbms_sql.parse(v_cursorid,
‘ALTER tablespace ‘||v_dba_tablespaces.TABLESPACE_NAME||’ default storage (next ‘
||to_number(a_next)||’ maxextents ‘||
to_number(a_maxextents)||’ ) ‘,dbms_sql.native);
status:=dbms_sql.execute(v_cursorid);
dbms_output.put_line(’Default storage for Tablespace: ‘|| v_dba_tablespaces.TABLESPACE_NAME|| ‘ ‘||
‘ NEXT and MAXEXTENTS set to ‘||to_number(a_next)||’ ‘||to_number(a_maxextents));
fetch c_dba_tablespaces into v_dba_tablespaces;
end loop;
close c_dba_tablespaces;
dbms_sql.close_cursor(v_cursorid);
exception
when others then
dbms_output.put_line(’Error…… ‘);
dbms_sql.close_cursor(v_cursorid);
raise;
end p_tbs_default_maxext_next;
/