SQL*Plus headsep Command

This was tested on Oracle 11.2. You can use headsep to tell Oracle when to continue a ttitle on a new line. With the following command, Oracle will continue a ttitle on a new line when it encounters an exclamation mark:

SQL> set headsep !

Set the pagesize small so that you don't need too much output to see headsep working:

SQL> set pages 10

Set the ttitle. This time do not include an exclamation mark, so the title will appear on 1 line:

SQL> ttitle 'Owners, Segments and Sizes'

Now see it working:

SQL> select owner, segment_name, bytes

  2  from dba_segments

  3  where rownum < 10

  4  /

Tue Sep 04                                    page    1

              Owners, Segments and Sizes

OWNER                SEGMENT_NAME              BYTES

-------------------- -------------------- ----------

SYS                  CON$                     393216

SYS                  BOOTSTRAP$                65536

SYS                  PROXY_DATA$               65536

SYS                  PROXY_ROLE_DATA$          65536

Tue Sep 04                                    page    2

              Owners, Segments and Sizes

OWNER                SEGMENT_NAME              BYTES

-------------------- -------------------- ----------

SYS                  OBJ$                    8388608

SYS                  FILE$                     65536

SYS                  UNDO$                     65536

SYS                  OBJERROR$                 65536

Tue Sep 04                                    page    3

              Owners, Segments and Sizes

OWNER                SEGMENT_NAME              BYTES

-------------------- -------------------- ----------

SYS                  OBJAUTH$                 917504

9 rows selected.

SQL>

Next, reissue the ttitle command but include an exclamation mark where you want the line break to appear:

SQL> ttitle 'Owners, Segments!and Sizes'

SQL>

Finally, rerun the SQL and see how the title is split over 2 lines:

SQL> select owner, segment_name, bytes

  2  from dba_segments

  3  where rownum < 10

  4  /

Tue Sep 04                                    page    1

                   Owners, Segments

                       and Sizes

OWNER                SEGMENT_NAME              BYTES

-------------------- -------------------- ----------

SYS                  CON$                     393216

SYS                  BOOTSTRAP$                65536

SYS                  PROXY_DATA$               65536

Tue Sep 04                                    page    2

                   Owners, Segments

                       and Sizes

OWNER                SEGMENT_NAME              BYTES

-------------------- -------------------- ----------

SYS                  PROXY_ROLE_DATA$          65536

SYS                  OBJ$                    8388608

SYS                  FILE$                     65536

Tue Sep 04                                    page    3

                   Owners, Segments

                       and Sizes

OWNER                SEGMENT_NAME              BYTES

-------------------- -------------------- ----------

SYS                  UNDO$                     65536

SYS                  OBJERROR$                 65536

SYS                  OBJAUTH$                 917504

9 rows selected.

SQL>