This discussion is archived
2 Replies Latest reply: Mar 26, 2013 10:21 AM by 9423755 RSS

Query output not aligned with column headers in SQL*Plus 11g

rob king Newbie
Currently Being Moderated
I've noticed that query results are often not aligned with column headers in SQL*Plus 11.2.0.3 (64-bit on Windows 7).

When I run a SQL statement with many columns, sometimes the data doesn't line up with the headers.

For example, take a look at this output:
SQL> select * from all_tables where rownum <= 5;

OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE     T S NES BUFFER_ FLASH_C CELL_FL ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER                  DEPENDEN COMPRESS COMPRESS_FOR DRO REA SEG RESULT_
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ---------- ---------- ----- -------- ----------- --------- --- ------------ - - --- ------- ------- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- ------------ --- --- --- -------
SYS                            CON$                           SYSTEM                                                                               VALID            10         40          1        255   65536    1048576           1  2147483645                       1               1 YES N      55682     270               0          0          0          26                         0                   0          1          1     N ENABLED        55682 02-JUL-12 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED YES                                DISABLED DISABLED              NO  NO  YES DEFAULT
SYS                            IND$                           SYSTEM                         C_OBJ#                                                VALID             0          0          0          0  139264    1048576           1  2147483645                       1               1 YES N      11020    3455               0          0          0          98                         0                   0          1          1     N ENABLED        11020 03-JUL-12 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED YES SYS                            DISABLED DISABLED              NO  NO  YES DEFAULT
SYS                            BOOTSTRAP$MIG                  SYSTEM                                                                               VALID            10         40          1        255   57344    1048576           1  2147483645                       1               1 YES N         57       3               0          0          0         300                         0                   0          1          1     N ENABLED           57 21-NOV-09 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED YES                                DISABLED DISABLED              NO  NO  YES DEFAULT
SYS                            UET$                           SYSTEM                         C_FILE#_BLOCK#                                                VALID             0          0          0          0          24576     1048576           1  2147483645                       1               1 YES N          0        448            0          0          0           0                         0           0          1          1     N ENABLED            0 21-NOV-09 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED YES SYS                            DISABLED DISABLED              NO  NO  YES DEFAULT
SYS                            COL$                           SYSTEM                         C_OBJ#                                                VALID             0          0          0          0  139264    1048576           1  2147483645                       1               1 YES N     223809    3455               0          0          0          66                         0                   0          1          1     N ENABLED       223809 27-JUN-12 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED YES SYS                            DISABLED DISABLED              NO  NO  YES DEFAULT
For the 1st, 2nd, 3rd and 5th records in the output, the status value ("VALID") doesn't appear under the "STATUS" header. It looks OK for the 4th record though.
Further down, all data for all records gets misaligned by the NUM_FREELIST_BLOCKS column header.

Any ideas what is going on here? And how to prevent this from happening?

Thanks.
  • 1. Re: Query output not aligned with column headers in SQL*Plus 11g
    Howard (... in Training) Pro
    Currently Being Moderated
    Bump. I have the same problem with Windows 7 (32-bit OS) and SQL*PLUS under Oracle Release 11.1.0.6.0. Thoughts?
  • 2. Re: Query output not aligned with column headers in SQL*Plus 11g
    9423755 Explorer
    Currently Being Moderated
    Hi

    Did you ever find a solution to this? If I run

    select * from all_tables where rownum <= 5;

    I get the default wrapped output i.e.
    SCOTT@ORCL> select * from all_tables where rownum <= 5;
    
    OWNER                          TABLE_NAME                     TABLESPACE_NAME
    ------------------------------ ------------------------------ ------------------------------
    CLUSTER_NAME                   IOT_NAME                       STATUS     PCT_FREE   PCT_USED
    ------------------------------ ------------------------------ -------- ---------- ----------
     INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS
    ---------- ---------- -------------- ----------- ----------- ----------- ------------ ----------
    FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
    --------------- --- - ---------- ---------- ------------ ---------- ---------- -----------
    AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE
    ------------------------- ------------------- ---------- ---------- ----- -------- -----------
    LAST_ANAL PAR IOT_TYPE     T S NES BUFFER_ FLASH_C CELL_FL ROW_MOVE GLO USE DURATION        SKIP_COR
    --------- --- ------------ - - --- ------- ------- ------- -------- --- --- --------------- --------
    MON CLUSTER_OWNER                  DEPENDEN COMPRESS COMPRESS_FOR DRO REA SEG RESULT_
    --- ------------------------------ -------- -------- ------------ --- --- --- -------
    SYS                            ICOL$                          SYSTEM
    C_OBJ#                                                        VALID             0          0
             0          0         139264      204800           1  2147483645                       1
                  1 YES N       9739       1555            0          0          0          32
                            0                   0          1          1     N ENABLED         9739
    10-MAR-13 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED
    YES SYS                            DISABLED DISABLED              NO  NO  YES DEFAULT
    
    SYS                            CON$                           SYSTEM
                                                                  VALID            10         40
             1        255          65536     1048576           1  2147483645                       1
                  1 YES N      15313         68            0          0          0          26
                            0                   0          1          1     N ENABLED        15313
    21-MAR-13 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED
    YES                                DISABLED DISABLED              NO  NO  YES DEFAULT
    
    SYS                            UNDO$                          SYSTEM
                                                                  VALID            10         40
             1        255          65536     1048576           1  2147483645                       1
                  1 YES N         21          1            0          0          0          59
                            0                   0          1          1     N ENABLED           21
    10-MAR-13 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED
    YES                                DISABLED DISABLED              NO  NO  YES DEFAULT
    
    SYS                            PROXY_ROLE_DATA$               SYSTEM
                                                                  VALID            10         40
             1        255          65536     1048576           1  2147483645                       1
                  1 YES N          0          0            0          0          0           0
                            0                   0          1          1     N ENABLED            0
    30-MAR-10 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED
    YES                                DISABLED DISABLED              NO  NO  YES DEFAULT
    
    SYS                            FILE$                          SYSTEM
                                                                  VALID            10         40
             1        255          65536     1048576           1  2147483645                       1
                  1 YES N          7          1            0          0          0          39
                            0                   0          1          1     N ENABLED            7
    12-MAR-13 NO               N N NO  DEFAULT DEFAULT DEFAULT DISABLED YES NO                  DISABLED
    YES                                DISABLED DISABLED              NO  NO  YES DEFAULT
    
    
    SCOTT@ORCL>
    Did you set some COLUMN XXX FORMAT ... commands?

    Jason

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points