How to increase buffer size in Oracle SQL Developer to view all records (there seems to be a certain limit set at default)? Any screen shots and/or tips will be very helpful.
This question is related to
sql
oracle
oracle11g
oracle-sqldeveloper
press f5 for running queries instead of f9. It will give you all the results in one go...
You can also edit the preferences file by hand to set the Array Fetch Size to any value.
Mine is found at C:\Users\<user>\AppData\Roaming\SQL Developer\system4.0.2.15.21\o.sqldeveloper.12.2.0.15.21\product-preferences.xml
on Win 7 (x64).
The value is on line 372 for me and reads <value n="ARRAYFETCHSIZE" v="200"/>
I have changed it to 2000 and it works for me.
I had to restart SQL Developer.
After you fetch the first 50 rows in the query windows, simply click on any column to get focus on the query window, then once selected do ctrl + end key
This will load the full result set (all rows)
Tools-> Preferences -> Database ->Worksheet
Enter desired size in "Max rows to print in a script"
Save changes.
Here is another cheat:
Limit your query if you don't really need all the rows. i.e.
WHERE rownum <= 10000
Then click on any cell of the results and do from your keyboard CTRL+END
. This will force SQL Developer to scroll until the bottom result of your query.
This has the advantage of keeping the default behavior and use this on demand.
Select Tools > Preferences > Database / Advanced
There is an input field for Sql Array Fetch Size but it only allows setting a max of 500 rows.
If you are running a script, instead of a statement, you can increase this by selecting Tools/Preferences/Worksheet and increasing "Max Rows to print in a script". The default is 5000, you can change it to any size.
It is easy, but takes 3 steps:
All rows will be fetched into the "Query Result" window!
after you retrieve the first 50 rows in the query windows, simply click a column to get focus on the query window, then once selected do ctrl + pagedown
This will load the full result set (all rows)
Source: Stackoverflow.com