{"id":11056,"date":"2018-04-01T01:12:50","date_gmt":"2018-03-31T23:12:50","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/"},"modified":"2018-04-01T01:12:50","modified_gmt":"2018-03-31T23:12:50","slug":"sqlplus-and-its-column-output","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/","title":{"rendered":"sqlplus and its column output"},"content":{"rendered":"

During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here):<\/p>\n

\"no_terminator\"<\/a><\/p>\n

Most people address this by using other tools like sqldeveloper (or sqlcl with sqlformat ansiconsole). However, a lot of people still use sqlplus.<\/p>\n

How can you address the output-format-issue with sqlplus?
\nActually you can format columns of course and specify only the columns you would like to see (instead of “select *”):<\/p>\n


\nSQL> select schemaname, osuser from v$session where sid=(select sid from v$mystat where rownum=1);
\n 
\nSCHEMANAME OSUSER
\n-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
\nCBLEILE oracle
\n 
\nSQL> column schemaname format a32
\nSQL> column osuser format a32
\nSQL> select schemaname, osuser from v$session where sid=(select sid from v$mystat where rownum=1);
\n 
\nSCHEMANAME OSUSER
\n-------------------------------- --------------------------------
\nCBLEILE oracle
\n<\/code><\/p>\n

But that’s annoying if you have to do it for a couple of columns for every sql-statement you interactively type in.<\/p>\n

The better alternative is to use a terminal emulation, which allows horizontal scrolling. Tanel Poder describes it in his “AOT Preparation Session” on Youtube: https:\/\/www.youtube.com\/watch?v=tC81PMO7ODw<\/a>.
\nInterestingly horizontal scrolling is available on Windows for ages. I.e. using cmd.exe or the Powershell you can define a “Screen Buffer Size” for the “Width” of the Window (in Properties -> Layout). Best is to set it under “Defaults” so that it’s available every time you open a cmd.exe or Powershell window.<\/p>\n

But what to use on e.g. Linux? Most terminal emulation software does not allow horizontal scrolling. Actually there are only very few terminal emulation products available, which allow it. One of the freely available products is terminator ( https:\/\/github.com\/software-jessies-org\/jessies\/wiki\/Terminator<\/a> ). After downloading the rpm on my Oracle Enterprise Linux server I installed it as follows:<\/p>\n


\n[root@localhost terminator]# rpm -i org.jessies.terminator.x86_64.rpm
\nerror: Failed dependencies:
\n\t\/usr\/bin\/ruby is needed by org.jessies.terminator-27.171.7083-2.x86_64
\n[root@localhost terminator]# yum install ruby
\n[root@localhost terminator]# rpm -i org.jessies.terminator.x86_64.rpm
\n[root@localhost terminator]# terminator
\n<\/code><\/p>\n

The prereqs for terminator are an installed Java Runtime Environment (JRE -> you may use the one provided within your ORACLE_HOME) and Ruby (as you can see above).
\nI started terminator once as root to install the necessary global terminfo. After that I can use it as e.g. the oracle-user:<\/p>\n


\n[oracle@localhost ~]$ terminator &
\n<\/code><\/p>\n

There are no wrapped lines due to the terminal size anymore:<\/p>\n

\"terminator1\"<\/a><\/p>\n

And you can scroll to the right:<\/p>\n

\"terminator2\"<\/a><\/p>\n

Running sqlcl with sqlformat set to ansiconsole the Terminator terminal emulation also helps for queries with many columns, which do not fit on the screen:<\/p>\n

\"terminator_sqlcl1\"<\/a><\/p>\n

Scrolled to the right:<\/p>\n

\"terminator_sqlcl2\"<\/a><\/p>\n

Besides the formatting issue, it’s of course recommended to use the utility rlwrap (readline wrapper) with sqlplus on Linux for command line history (get previous command lines with the up and down key) and easy command line editing. Actually rlwrap is much better than the history available in sqlplus of 12.2. I do recommend watching the youtube video from Tanel Poder mentioned above, because he also shows that rlwrap can be configured to provide command completion (for Oracle PLSQL-objects and reserved words) as well.<\/p>\n

When installing the dbi services DMK Management Kit ( https:\/\/www.dbi-services.com\/dmk<\/a> ) on Linux then rlwrap is of course included.<\/p>\n

So sqlcl is cool, but sqlplus also still has its right to exist \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"

During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here): Most people address this by using other […]<\/p>\n","protected":false},"author":35,"featured_media":11060,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,59],"tags":[1333,96,1334,673,609,985],"type_dbi":[],"class_list":["post-11056","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-database-management","category-oracle","tag-column","tag-oracle","tag-output","tag-sqlcl","tag-sqldeveloper","tag-sqlplus"],"acf":[],"yoast_head":"\nsqlplus and its column output - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"sqlplus and its column output\" \/>\n<meta property=\"og:description\" content=\"During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here): Most people address this by using other […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-31T23:12:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1159\" \/>\n\t<meta property=\"og:image:height\" content=\"702\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"sqlplus and its column output\",\"datePublished\":\"2018-03-31T23:12:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/\"},\"wordCount\":494,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg\",\"keywords\":[\"column\",\"Oracle\",\"output\",\"SQLcl\",\"SQLDeveloper\",\"sqlplus\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/\",\"name\":\"sqlplus and its column output - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg\",\"datePublished\":\"2018-03-31T23:12:50+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg\",\"width\":1159,\"height\":702},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"sqlplus and its column output\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\/\/www.dbi-services.com\",\"https:\/\/x.com\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"sqlplus and its column output - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/","og_locale":"en_US","og_type":"article","og_title":"sqlplus and its column output","og_description":"During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here): Most people address this by using other […]","og_url":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/","og_site_name":"dbi Blog","article_published_time":"2018-03-31T23:12:50+00:00","og_image":[{"width":1159,"height":702,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg","type":"image\/jpeg"}],"author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"sqlplus and its column output","datePublished":"2018-03-31T23:12:50+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/"},"wordCount":494,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg","keywords":["column","Oracle","output","SQLcl","SQLDeveloper","sqlplus"],"articleSection":["Database Administration & Monitoring","Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/","url":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/","name":"sqlplus and its column output - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg","datePublished":"2018-03-31T23:12:50+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/terminator_sqlcl1.jpg","width":1159,"height":702},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sqlplus-and-its-column-output\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"sqlplus and its column output"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11056","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11056"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11056\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/11060"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11056"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11056"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11056"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11056"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}