How to get rid of scrolling for long SQL output in Oracle

For large output which is not able to fit in a single window, scrolling the results can skip data while it looks messy as well. to get rid of scrolling, you can use the command “set pause on”. With pause ON, the output will come in small chunks and will continue only when you press enter. It’s output resembles that of a more command which we use in unix.

SQL> set pause on
SQL>
SQL> select * from SMON_SCN_TIME
  2  ;

    THREAD    TIME_MP TIME_DP      SCN_WRP    SCN_BAS
———- ———- ——— ———- ———-
         1 1301818161 03-APR-11          0 3498484333
         1 1301818565 03-APR-11          0 3498484683
         1 1301818967 03-APR-11          0 3498485031
         1 1301819370 03-APR-11          0 3498485383
         1 1301819772 03-APR-11          0 3498485730
         1 1301820173 03-APR-11          0 3498486079
         1 1301820574 03-APR-11          0 3498486430
         1 1301820977 03-APR-11          0 3498486780
         1 1301821379 03-APR-11          0 3498487132
         1 1301821785 03-APR-11          0 3498487482
         1 1301822187 03-APR-11          0 3498487851
[waiting for user to press enter]

To disable this function, you can use “set pause off”. A part from this discussion, you can always use help command to see all the SQL PLUS commands as shown below:

SQL> help index

Enter Help [topic] for help.

@             COPY         PAUSE                    SHUTDOWN
@@            DEFINE       PRINT                    SPOOL
/             DEL          PROMPT                   SQLPLUS
ACCEPT        DESCRIBE     QUIT                     START
APPEND        DISCONNECT   RECOVER                  STARTUP
ARCHIVE LOG   EDIT         REMARK                   STORE
ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
BREAK         EXIT         REPHEADER                TTITLE
BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE

CLEAR         HOST         RUN                      WHENEVER OSERROR
COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
COMPUTE       LIST         SET
CONNECT       PASSWORD     SHOW

SQL>

To explore a single command help use help followed by a command.

SQL> help PAUSE

PAUSE
—–

Displays the specified text then waits for the user to press RETURN.

PAU[SE] [text]

Not available in iSQL*Plus

SQL>

Adnan Khurshid

Adnan Khurshid, the author of this article, has been working in a telecommunication sector since 2007. He has worked there as a VAS (Value Added Services) engineer and has excelled remarkably in the field. Working in this field has been his passion and he has always made efforts to keep himself up to date. Find more about him on LinkedIn

More Posts

Tags: long sql output, set pause off, set pause on