strange SELECT behavior, possibly related to SYSTEM tblspc being full

logging into to sqlplus as appowner, in which schema is table big_table:

select * from big_table -> no records returned select count(*) from big_table -> over a million records select field1, field2, field3, etc etc (all fields) from big_table -> over a million records

appowener has full permissions on the table.

first time i saw this, the cause was the SYSTEM tablespace being full. slightly different issue, in that it was specific fields in the table that were showing the problem.

i.e.:

select * from medium_table -> no records returned select field1, field2 from medium_table -> 1000 rows returned select field3 from medium_table -> no records returned select field, field2, field3 from medium_table -> no records returned

adding a few hundred MB to the full SYSTEM tblspc resolved it..

now i'm seeing it on another DB, a different table. problem first showed itself when SYSTEM tblpsc was almost full, but this time increasing the tablespace doesnt solve the problem, the other difference is there is no specific field(s) that is causing the problem: select all fields comma-separated is ok, select * is not.

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

is this known oracle behaviour? any explanation for it? how to resolve it? any help appreciated.

--
reply to group