column width change in 12c

In 11g I used to have 30 characters width in my dictionary

SQL> select table_name, column_name from user_tab_columns;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
BONUS                          COMM
BONUS                          SAL
BONUS                          JOB
BONUS                          ENAME
DEPT                           LOC
DEPT                           DNAME
DEPT                           DEPTNO
EMP                            DEPTNO
EMP                            COMM
EMP                            SAL
EMP                            HIREDATE

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP                            MGR
EMP                            JOB
EMP                            ENAME
EMP                            EMPNO
SALGRADE                       HISAL
SALGRADE                       LOSAL
SALGRADE                       GRADE

18 rows selected.

Which was pretty nice to read with no setting.

Now in 12c it is ugly enought to make your eyes tired

SQL> select table_name, column_name from user_tab_columns;

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
DEPT
DEPTNO

DEPT
DNAME

DEPT
LOC

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
EMPNO

EMP
ENAME

EMP
JOB

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
MGR

EMP
HIREDATE

EMP
SAL

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
COMM

EMP
DEPTNO

BONUS
ENAME

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
BONUS
JOB

BONUS
SAL

BONUS
COMM

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
SALGRADE
GRADE

SALGRADE
LOSAL

SALGRADE
HISAL

18 rows selected.

SQL>

This is due to a change of the width of the underlying column in the dictionary. Probably a good-news for our data modeling colleagues that it may be 128 in future.

But currently it is only 30 characters in 12c. So why not format it correctly ?

Simply add the format in $ORACLE_HOME/sqlplus/admin/glogin.sql

col TABLE_NAME for a30
col COLUMN_NAME for a30 

DISCLAIMER: it’s fine to add UNIQUE_KEY_LEVEL_NAME or REFERENCED_TRIGGER_NAME, but you may have application tables that have columns called OWNER or USER, it is probably safer to not assume they are all smaller than 30 chars, so don’t add common names.