Amount of data fetched by query

[Praveen Bezawada]

Hi
On HP-UX 1100 using Oracle 8.0.5 which is the variable which specified the amount of data that can be fetched by a single query.
Thanks in advance
Praveen

[Steve Slade]

Praveen,

I am not sure if I am reading your question correctly - Do you wish to limit the amount of rows returned from your database query? If so then the sql clause rownum will do this.

Eg. Select * from my table where rownum < 10 will limit to 9 rows.

Otherwise, are you having problems with a long running query which fails? In which case it could be that you are using up too much memory sorting, etc. This limit can be increased by the kernel parm maxdsize which sets the amount of memory 1 process can have in pages (4k I believe)

[Praveen Bezawada]

I am talking about result of a query which gives large amount of data. I was wondering if there is any configurable parameter in Orcale which specifies the amount of data that can be fetched in a single sql query.

Thanks
Praveen

[Alexander M. Ermes]

Are we talking about access with SQLPlus,
Forms, Reports or ODBC ?
Within Forms, Reports or MS-ACCESS you have a prefetch count parameter. I have not seen such a thing in SQLPlus, but perhaps there is somebody, who can correct that.
Rgds
Alexander M. Ermes

[Steve Slade]

Praveen,

The only Oracle parameter which will effect the amount of data returned in a query is the one above (rownum). Otherwise, Oracle will always (Memory/Disk allowing), return every record which matches your query - even if that means the complete contents of your table(s) in your query.