Ref( 2 Million Records Cursor)

Hi,

I have a cursor in pl/sql block which opens 2 million record at a time.
The size of the data could be 200MB. I would like to know where this data will be stored until the pl/sql block successfully completed.

I believe PGA, is it right or temp tablespace. The cursor query has order by clause.

Thanks,

Hi

It would be stored in the SGA database buffer cache offcourse..but not your 2 million rows..the answer would deepnd on the array size of the calling program ..whaether it oci ,sqlplus,java or etc..and for sorting the sort would be split into sort runs..

regards
Hrishy

Hi,

It is a sql file which has the pl/sql block on the databse server.
It is being called by the oracle apps. I believe sql file is being executed in one of the session made by the oracle apps with database.

So how do I know the array size of the session made by the oracle apps with DB.

What is the default session array_size?.


Thanks,

Hi

The array size is dependent upon the tool..i am not aware of oracle apps..but for

sqlplus the default is 15
JDBC the defualt is 10
Pro*c the default is 2
OCI it is 1

you know what even if your server memory is unlimited and you have an unlimited sga and assuming oracle stored the 2 million rows for you in the SGA then..you need to have all clients with unlimited memory tooo..:-D which i guess is impossible :-D

if you are sorting these 2 Million record then oracle will materialize this sort in the temp tablespace..you make sure you have a large temporary tablespace..

regards
Hrishy