[sql] How to increase buffer size in Oracle SQL Developer to view all records?

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

The answer is


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:

  1. In SQL Developer, enter your query in the "Worksheet" and highlight it, and press F9 to run it. The first 50 rows will be fetched into the "Query Result" window.
  2. Click on any cell in the "Query Result" window to set the focus to that window.
  3. Hold the Ctrl key and tap the "A" key.

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)


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to oracle11g

Convert timestamp to date in Oracle SQL Query to display all tablespaces in a database and datafiles Oracle Installer:[INS-13001] Environment does not meet minimum requirements Forgot Oracle username and password, how to retrieve? Extract number from string with Oracle function How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 Add days Oracle SQL How to determine tables size in Oracle ORA-28000: the account is locked error getting frequently Oracle listener not running and won't start

Examples related to oracle-sqldeveloper

how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Extract number from string with Oracle function How to run .sql file in Oracle SQL developer tool to import database? SQL Developer with JDK (64 bit) cannot find JVM How do I view the Explain Plan in Oracle Sql developer? NLS_NUMERIC_CHARACTERS setting for decimal copy from one database to another using oracle sql developer - connection failed Oracle SqlDeveloper JDK path Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection?