"Out of Memory" error when viewing data from table with a lot of columns and rows. (4312618)

"Out of Memory" error when viewing data from table with a lot of columns and rows.

Error received when viewing data in Schema Browser | Tables | Data tab or from SQL Editor | Data Grid tab from a table that contains a large amount of columns and rows:

"Out of Memory"

This error does not occur when the number of column or rows displayed are limited.

WORKAROUND 1:

1. Select View | (Toad) Options | Oracle | General.
2. Decrease the OCI array buffer size to a smaller amount or to Oracle's default value of 25.

(This may not be available in certain versions of Toad). 

WORKAROUND 2:

View | Toad Options | Data Grid | Data - increase the 'stop data fetches when available memory reaches __ MB' option to 128mb (default is 50mb).

WORKAROUND 3:

Use F9 (Execute as Statement) instead of F5 (Execute as Script) so that Toad will only fetch about 500 records rather than all the records.

If using F5, adjust the following options, Toad Options | Script

• disable "Show Script Grids"
• disable "Maintain Script History". 

(The amount to fetch at once is automatically optimized.)

WORKAROUND 4:

Limit rows returned to required data using 'WHERE' condition and ORDER BY clauses.

Note, beyond number of records, the number of columns could also play a part in regards to the size of the data returned and displayed in the data grid.

QCM33989

Information regarding the OCI Array Buffer Size value: Q: What is the OCI Buffer Array Size option for in the View? A: After a SELECT query is executed, Toad must retrieve the rows from the Oracle server to the computer. Toad does not retrieve the rows all at once, nor one at a time (unless there is a LONG or LOB column involved). Toad retrieves the rows in blocks. The number of rows retrieved in each block is the number of rows specified for the "OCI Array Buffer Size". TOAD defaults to 25 because SQL*Plus defaults to 25. An optimal setting is more like 500 or 1000 because if the dataset has 1,000 rows and the OCI Array Buffer size is set to 25, then TOAD has to make 40 (that's 1000 / 25) round trips across the network to retrieve all of the rows. If the OCI Array Buffer Size is set to 500, then only two trips will be needed to obtain the 1,000 rows. The only disadvantage to a higher setting of OCI Array Buffer Size is that TOAD must allocate memory to hold that many rows prior to each fetch. If that many rows are actually fetched, there is no loss. On the other hand, if the number of rows fetched is less than that, then Toad allocated some memory that is not going to be released until the cursor is freed. Luckily, this is a trivial amount of memory, in the grand scheme of things.