Re: strange SELECT behaviour when SYSTEM tablespace is full

niz wrote:
> when the 2GB SYSTEM tablespace is 100% full, SELECT begins to behave
> unusually:
>
> logging in with the schema-owner:
>
> select count(*) from BMF -> 8000 row.
> select * from BMF -> 0 rows
> select field1, field2 from BMF -> 8000 rows
> select field3 from BMF -> 0 rows
> select field4, field 5 from BMF -> 8000 rows
> select field, field2, field3, field4, field5 from BMF -> 8000 rows
>
> for some reason it stops liking field3
>
> logging in with sys gives the correct results though:
>
> select count(*) from BMF -> 8000 row.
> select * from BMF -> 8000 rows
> select field1, field2 from BMF -> 8000 rows
> select field3 from BMF -> 8000 rows
> select field4, field 5 from BMF -> 8000 rows
> select field, field2, field3, field4, field5 from BMF -> 8000 rows
>
> and it also works when the SYSTEM tablespace is increased by 200MB.
>
> there are no column-level privileges in use here. its oracle 9.2.0.5.0
> production.
>
> why do SELECT statements require SYSTEM tablespace, and why doesnt
> oracle give an error instead of suddenly losing columns? is it a known
> oracle bug? is there a case# for this?
>
> thanks.

A couple of questions:

1. What have you put into the system tablespace that doesn't belong there? You can start the list with whatever BMF is.
2. Why are you on 9.2.0.5 rather than applying the available patches?

Eliminate DBA error before looking at what is wrong with the product.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org