5 Replies Latest reply on Mar 25, 2015 12:05 PM by thatJeffSmith-Oracle

    Altering row limit from 5,000 to 1,000,000

    df80d43e-3874-4f42-a66a-54593872dccb

      I'm using Oracle SQL Developer Version 1.1.3. I have used the menu to get 1,000,000 rows as the result of my query but nothing appears. When I reduce that amount by a factor of 10 (but not more than 100,000), many data rows are suppressed. Please assist.SQL Developer

        • 1. Re: Altering row limit from 5,000 to 1,000,000
          thatJeffSmith-Oracle

          Step 1: Upgrade. Your version is about 10 years old.

           

          Step 2: Why do you want 1,000,000 rows?

           

          Are you trying to create a file?

          • 2. Re: Altering row limit from 5,000 to 1,000,000

            Please assist with what?

             

            You haven't posted any question or issue to assist with.

             

            The thread subject mentions altering row limit from 5,000 to 1,000,000 but the text says NOTHING about 5,000 or what your problem is.

             

            Neither Oracle nor Sql Developer 'suppresses' rows. But unless you add an ORDER BY to a query the rows may be returned in a random or arbitray order. So if you are looking for a particular row it could be the LAST row actually returned.

             

            What PROBLEM are you trying to solve?


            • 3. Re: Altering row limit from 5,000 to 1,000,000
              thatJeffSmith-Oracle

              >>Neither Oracle nor Sql Developer 'suppresses' rows
              Yes, we do - check your worksheet preferences.

               

              Max rows to print in a script: Limits the number of rows displayed.

               

              Max lines in script output: Limits the number of lines output.

               

              If you want a file, use the spool command and we'll write everything to the file regardless of that setting.

              • 4. Re: Altering row limit from 5,000 to 1,000,000

                Jeff Smith Sqldev Pm-Oracle wrote:

                 

                >>Neither Oracle nor Sql Developer 'suppresses' rows
                Yes, we do - check your worksheet preferences.

                 

                Max rows to print in a script: Limits the number of rows displayed.

                 

                Max lines in script output: Limits the number of lines output.

                 

                If you want a file, use the spool command and we'll write everything to the file regardless of that setting.

                Neither of those 'suppress' any rows from a worksheet query for me.

                 

                Maybe we are talking about different things?

                 

                I read OPs post as performing a simple query in a worksheet:

                I have used the menu to get 1,000,000 rows as the result of my query but nothing appears. When I reduce that amount by a factor of 10 (but not more than 100,000), many data rows are suppressed.

                I set both 'max rows' and 'max lines' values to 50 and exited then relaunched sql dev.

                 

                I used a worksheet and a simple SELECT * query to select ALL rows from a table with 4000+ rows. The result window shows them being fetched 50 at a time and NONE of them are suppressed as more and more groups of 50 (my array fetch size value in preferences) are fetched. and the result window fetches them 50 at a time just like the fetch value is set to.

                 

                No rows are being suppressed. If I do a CTRL-END and go to the END of the result set I see the end of ALL 4000+ rows - no limit of 50 and the count shows the correct total number of rows.

                 

                I don't know if OP is doing the 'script' thing you refer to - don't know for sure. The usual thing I see is someone not seeing a particular row in that first 20/30/etc that gets returned and wondering where it is. Usually it is because they think that a query will return the rows in the same order that the user inserted them - which it won't of course.

                • 5. Re: Altering row limit from 5,000 to 1,000,000
                  thatJeffSmith-Oracle

                  execute as a script, F5