Tuesday, September 04, 2012

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>

No comments: