ORA-03232: unable to allocate an extent of 100 blocks from tablespace 3

Problem Description:
----------------------------------------------------------------------------
Error : ORA-03232: unable to allocate an extent of %s blocks from tablespace 3
Server :Oracle 8.1.6, Window 2000 Advanced Server
Domain Controller: Window 2000 Advanced Server
Client:Oracle Net 8 (8.1.6),Window XP SP2
Case:
One of my developers was trying to run his stored proc from the backend and the stored proc was just taking a lot of time and just gang indefinitely. Using OEM I found that this particular session from the developer was running a hash join and categorised it as Long Operation. The same stored procedure runs excellent in one test environment as well as production. This happens only in the development environment. The error I got is
ORA-03232: unable to allocate an extent of 58 blocks from tablespace 3
ORA-06512: at "IDBOWNER.SOPVCCONFIG", line 185
ORA-06512: at line 1

Problem Explanation:
====================
You can get the tablespace 3 information by running the SQL
select * from v$tablespace;
TS# NAME
---------- ------------------------------
0 SYSTEM
6 IDB
2 RBS
3 TEMP
5 AUD
4 INDX
6 rows selected.
Now you know the tablespace 3 is TEMP
The following query gives you more info:
SQL> select initial_extent, next_extent, min_extents, max_extents, pct_increase,
min_extlen, status from dba_tablespaces where tablespace_name='TEMP';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS
-------------- ----------- ----------- ----------- ------------ ---------- ---------
262144 262144 2 5 262144 ONLINE
Cause:

• SELECT" statement has a "order by" clause, ORACLE requires the temporary tablespace for sorting
• An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value that is greater than the tablespace's NEXT value

Solution Description:
====================
You can:

• Set the tablespace to autoextend mode: ALTER DATABASE DATAFILE filename AUTOEXTEND on NEXT 10M;
• Or, Increase the value of NEXT for the tablespace using ALTER TABLESPACE DEFAULT STORAGE or decrease the value of HASH_MULTIBLOCK_IO_COUNT
• Or, Set the parameter HASH_MULTIBLOCK_IO_COUNT = 0 in the ini file and restart the database, This satisfies the condition already that NEXT > hash_multiblock_io_count (Not good action)

TrungNEMO 3:34 AM Tags : 3.Oracle Help

Subscribe by Email

Follow Updates Articles from This Blog via Email