Setting sql*Plus column format

If you use sqlplus to query the dba*/v$ tables to find the state of the database, you often have to format the columns to get a readable output. One has to set pagesize, time on or timing on and so on to get output in readable form. . It is a lot less stressful to set these sql*plus commands are set in sqlplus/admin/glogin.sql. . Following sql will set all columns having size greater than 30 to format 30 so that you can view the results in readable way without having to set column formats again and again.

set head off feedback off verify off pages 0 lines 120 trimspool on timing off termout off spool /tmp/colform.sql select 'col '||column_name||' for a'||case when column_name like '%TYPE' or column_name like '%OWNER' then 12 when column_name like '%TEXT%' then 80 else 30 end ||' word_wrapped' str from dba_tab_columns where data_length > 30 and data_type = 'VARCHAR2' group by column_name; spool off @/tmp/colform.sql set head on feedback on verify on pages 20000 lines 120 trimspool on timing on time on termout on .