Extents allocation problem

[parbhani]

Hi all,

I am on Oracle 8.1.7, we have all tablespaces locally managed, including the temp tablespace.
Please have a look at the following situation.

SQL> select count(*) from sample_v ;
select count(*) from sample_v
*
ERROR at line 1:
ORA-03232: unable to allocate an extent of 63 blocks from tablespace 1

sample_v is a view , which is a very huge query.
Now my query is ,
1) Why its shown as tablespace 1, there is no tablespace with such name
2) If I say that the query is badly written and using too much temp space, there is more than 2GB temp space

Also the database is not our production DB, so there need not be huge traffic or temp usage .

Please guide

 

[SantaMufasa]

Parbhani,

To find out which tablespace is "Tablespace 1", issue this query:

"SELECT * FROM V$TABLESPACE;"

Whatever that tablespace is, it would also be interesting to find out what the largest extent of free bytes is for that tablespace:

(Query A) "select max(bytes) from dba_free_space where tablespace_name = 'name_of_ts_from_above_query';"
Also,
(Query B) "select 63 * value from v$parameter where name = 'db_block_size';" -- 63 came from your error msg.

Is the result from Query B > Query A? If so, add another (or extend a) datafile for your Tablespace 1. If not, Oracle recommends, "Increase the value of NEXT for the tablespace using ALTER TABLESPACE DEFAULT STORAGE or decrease the value of HASH_MULTIBLOCK_IO_COUNT."

Dave
Sandy, Utah, USA @ 17:52 GMT, 10:52 Mountain Time

 

[karluk]

Specifically, the tablespace with the error can be identified with the query

select name from v$tablespace
where ts# = 1;

 

[sem]

I suppose that the real problem is that SORTING is probably made in SYSTEM tablespace, so instead of extending tablespace, it would be better to alter user

Regards, Dima

 

[sem]

To explain my assumption: SYSTEM is the only mandatory tablespace, so I suppose its the best candidate to be #1. _V in sample_v may mean VIEW and the only reason for extending tablespace during SELECT is extending temporary tablespace for sorting.

Regards, Dima

 

[SantaMufasa]

Dima,

Yours would be a good supposition if Oracle started numbering tablespaces at "1", but Oracle starts numbering them at "0". Therefore, Tablespace 0 is "SYSTEM", and Tablespace 1 is whichever tablespace you create next; I've checked three databases as an example and saw three different Tablespaces #1: RBS, TEMP, TOOLS...

So that is why I'd love it if Parbhani would help us help him by responding and solving the mystery as to which, on his system, is Tablespace 1.

Dave
Sandy, Utah, USA @ 16:58 GMT, 09:58 Mountin Time

 

[sem]

Ok, Dave, ought to check it myself, thak you.
And read carefully the question at least: sample_v is indeed a view, but that results from the text of question, not from its name

Parbhani, read the Note 125271.1 on Metalink, probably it may help to resolve it.

Regards, Dima

 

[sem]

Me again. The second line was adressed to myself, not to Dave

Regards, Dima

 

[saintSlen]

Cause: An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value that is greater than the tablespace's NEXT value.

Action: Increase the value of NEXT for the user's temp tablespace.


~L