Home » RDBMS Server » Server Administration » Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3
Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3 [message #404797] Sun, 24 May 2009 01:07 Go to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
I have encounter the following message when I tried to run a query.
"ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3",
and I have check the tablespace,
tablespace name : temp
initial extend : 131072
next extend : 131072
max extend : 4096
pct increate : 0

and I have change the tablespace,
tablespace name : temp
initial extend : 262144
next extend : 262144
max extend : 4096
pct increate : 0

still does not solve my problem, please advise. Thank you.



Re: Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3 [message #404801 is a reply to message #404797] Sun, 24 May 2009 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is there is no more space in the tablespace, increase it.

Always post your Oracle version (with 4 decimals), always copy and paste the error, always copy and paste what you do, DO NOT just tell or interpret it.

There are a couple of things strange in your post.

Regards
Michel

[Updated on: Sun, 24 May 2009 01:43]

Report message to a moderator

Re: Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3 [message #404844 is a reply to message #404797] Sun, 24 May 2009 23:25 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Hi,greentea,please specify the oracle version.By the way in general the error u asked for may be having one of these causes

1)Your SELECT statement has a "order by" clause, which requires the temporary tablespace for sorting

2) An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value that is greater than the tablespace's NEXT value

Solution to the cause
1)Set the tablespace to autoextend mode: ALTER DATABASE DATAFILE filename AUTOEXTEND on NEXT 10M;
2)Increase the value of NEXT for the tablespace using ALTER TABLESPACE DEFAULT STORAGE or decrease the value of HASH_MULTIBLOCK_IO_COUNT
3) 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 (as per oracle documentation it is not recommended to modify this parameter)
Re: Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3 [message #404869 is a reply to message #404844] Mon, 25 May 2009 01:58 Go to previous messageGo to next message
rakesh_sni
Messages: 11
Registered: September 2008
Location: M.P.
Junior Member
drop the tablespace temp and recreate it.
Re: Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3 [message #404874 is a reply to message #404869] Mon, 25 May 2009 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rakesh_sni wrote on Mon, 25 May 2009 08:58
drop the tablespace temp and recreate it.

Why not drop the database and recreate it?

Regards
Michel

Re: Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3 [message #404887 is a reply to message #404874] Mon, 25 May 2009 03:28 Go to previous messageGo to next message
rakesh_sni
Messages: 11
Registered: September 2008
Location: M.P.
Junior Member
what is wrong sir,data is there,that is temp tablespace so i suggest that dropping,any thing wrong sir ,pl. tell
Re: Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3 [message #404891 is a reply to message #404887] Mon, 25 May 2009 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
that is temp tablespace

Who say that?
It is not because a tablespace is named temp that it is a temporary tablespace. You can't know and so can destroy OP's data.

Regards
Michel
Re: Hit ORA-03232: unable to allocate an extent of 18 blocks from tablespace 3 [message #405057 is a reply to message #404891] Tue, 26 May 2009 03:43 Go to previous message
rakesh_sni
Messages: 11
Registered: September 2008
Location: M.P.
Junior Member
i cann't do sir but i read in net,so i suggest that.
Thanks
Previous Topic: Failed verification check
Next Topic: Archive log is frequently filling
Goto Forum:
  


Current Time: Tue Dec 06 06:45:59 CST 2016

Total time taken to generate the page: 0.08878 seconds