How to increase buffer size in Oracle SQL Developer to view all records

Question

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

User · Accepted Answer

https   forums oracle com forums thread jspa threadID 447344  The pertinent section reads      There s no setting to fetch all records  You wouldn t like SQL   Developer to fetch for minutes on big tables anyway  If  for 1   specific table  you want to fetch all records  you can do Control-End   in the results pane to go to the last record  You could time the   fetching time yourself  but that will vary on the network speed and   congestion  the program  SQL Plus will be quicker than SQL Dev because   it s more simple   etc    There is also a button on the toolbar which is a  Fetch All  button   FWIW Be careful retrieving all records  for a very large recordset it could cause you to have all sorts of memory issues etc   As far as I know  SQL Developer uses JDBC behind the scenes to fetch the records and the limit is set by the JDBC setMaxRows   procedure  if you could alter this  it would prob be unsupported  then you might be able to change the SQL Developer behaviour

User · Answer

Tools-  Preferences -  Database - Worksheet   Enter desired size in  Max rows to print in a script   Save changes

User · Answer

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

User · Answer

Here is another cheat   Limit your query if you don t really need all the rows  i e   WHERE rownum  lt   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

User · Answer

press f5 for running queries instead of f9  It will give you all the results in one go

User · Answer

You can also edit the preferences file by hand to set the Array Fetch Size to any value   Mine is found at C  Users  lt user gt  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  lt value n  ARRAYFETCHSIZE  v  200   gt   I have changed it to 2000 and it works for me   I had to restart SQL Developer

User · Answer

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

User · Answer

It is easy  but takes 3 steps    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  Click on any cell in the  Query Result  window to set the focus to that window  Hold the Ctrl key and tap the  A  key    All rows will be fetched into the  Query Result  window

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to oracle11g

Examples related to oracle-sqldeveloper