Re: strange SELECT behavior, possibly related to SYSTEM tblspc being full

On Jul 19, 8:20 pm, niz <n..._at_infidel.freeserve.co.uk> wrote:
> 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

I would suggest SQL tracing (google for 10046 trace). You may see recursive SQL fail that is being handled by an application exception handler.

HTH, Steve Received on Thu Jul 19 2007 - 20:44:02 CDT