Ferhat's Blog

There will be only one database

Pivoting Sqlplus output of a single row in multiple columns with column command

Posted by fsengonul on December 10, 2014

When there are so many columns to fit in a screen for a single row , you may experience difficulties like this one:

 

13:23:53 SYSTEM@NODS 10-DEC-14 SQL> select * from gv$session where inst_id=1 and sid=479;                                                                                                                                                  

   INST_ID SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME           COMMAND    OWNERID TADDR            LOCKWAIT         STATUS   SERVER       SCHEMA#
---------- ---------------- ---------- ---------- ---------- ---------------- ---------- --------------- ---------- ---------- ---------------- ---------------- -------- --------- ----------
SCHEMANAME                     OSUSER          PROCESS                  MACHINE                                                                PORT TERMINAL
------------------------------ --------------- ------------------------ ---------------------------------------------------------------- ---------- ---------------
PROGRAM                                          TYPE       SQL_ADDRESS      SQL_HASH_VALUE SQL_ID         SQL_CHILD_NUMBER SQL_EXEC_ SQL_EXEC_ID PREV_SQL_ADDR    PREV_HASH_VALUE PREV_SQL_ID
------------------------------------------------ ---------- ---------------- -------------- -------------- ---------------- --------- ----------- ---------------- --------------- -------------
PREV_CHILD_NUMBER PREV_EXEC PREV_EXEC_ID PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE
----------------- --------- ------------ --------------------- ------------------------- --------------- ------------------- ----------------------------------------------------------------
MODULE_HASH ACTION                                                           ACTION_HASH CLIENT_INFO                      FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK#
----------- ---------------------------------------------------------------- ----------- -------------------------------- -------------------- ------------- -------------- ---------------
ROW_WAIT_ROW# TOP_LEVEL_CALL# LOGON_TIM LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION
------------- --------------- --------- ------------ --- ------------- ---------- --- -------------------------------- -------- -------- -------- ----------------------
CLIENT_IDENTIFIER                                                BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCK FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION       SEQ#     EVENT#
---------------------------------------------------------------- ----------- ----------------- ---------------- ----------- ----------------------- ---------------------- ---------- ----------
EVENT                                                            P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------
P2TEXT                                                                   P2 P2RAW            P3TEXT                                                                   P3 P3RAW            WAIT_CLASS_ID
---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- -------------
WAIT_CLASS# WAIT_CLASS                                                        WAIT_TIME SECONDS_IN_WAIT STATE               WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO SERVICE_NA
----------- ---------------------------------------------------------------- ---------- --------------- ------------------- --------------- -------------------- -------------------------- ----------
SQL_TRAC SQL_T SQL_T SQL_TRACE_ SESSION_EDITION_ID CREATOR_ADDR     CREATOR_SERIAL# ECID
-------- ----- ----- ---------- ------------------ ---------------- --------------- ----------------------------------------------------------------
         1 000000055D16C498        479      11661  223581723 0000000548D68818       1482 xxxxxxxxxxx                0 2147483644                                   INACTIVE DEDICATED       1482
xxxxxxxxxxxxx                      extusr          1234                     xxxxxxxx                                                              53940 unknown
JDBC Thin Client                                 USER       00                            0                                                       00000004F220CFA0      1234116758 d5j84z54sy84q
                1 10-DEC-14     16780596                                                                                     JDBC Thin Client
 2546894660                                                                            0                                               3536945      69354518             32        79591555
            0              94 10-DEC-14           44 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
                                                                 NO HOLDER                                      NO HOLDER                                                       29823        354
SQL*Net message from client                                      driver id                                                        1413697536 0000000054435000
#bytes                                                                    1 0000000000000001                                                                           0 00                  2723168908
          6 Idle                                                                      0              44 WAITING                    44030050                   -1                          0 xxxx
DISABLED FALSE FALSE FIRST EXEC                100 0000000548D68818              47


Elapsed: 00:00:00.01
13:24:11 SYSTEM@NODS 10-DEC-14 SQL> 

 

I’ve been using Tanel Poder’s printtab script for years in order to pivot the output.
And that script uses Tom Kyte’s code.
Yet , there is no end for being lazy. It becomes tedious to scroll up and down for finding the column(or row after pivot).
So I’ve combined Tanel Poder’s script with linux column function and here is the result:

 

13:32:56 SYSTEM@NODS 10-DEC-14 SQL> sta sesone 1 479                                                                                                                                                                                       
INST_ID                       : 1					PREV_EXEC_ID                  :						SEQ#                          : 121
SADDR                         : 000000055D16C498			PLSQL_ENTRY_OBJECT_ID         :						EVENT#                        : 325
SID                           : 479					PLSQL_ENTRY_SUBPROGRAM_ID     :						EVENT                         : PX Deq: Execution Msg
SERIAL#                       : 11745					PLSQL_OBJECT_ID               :						P1TEXT                        : sleeptime/senderid
AUDSID                        : 224545295				PLSQL_SUBPROGRAM_ID           :						P1                            : 268632063
PADDR                         : 0000000548D60258			MODULE                        : SQL*Plus				P1RAW                         : 000000001002FFFF
USER#                         : 1189					MODULE_HASH                   : 3669949024				P2TEXT                        : passes
USERNAME                      : xxxxxxx					ACTION                        :						P2                            : 54
COMMAND                       : 2					ACTION_HASH                   : 0					P2RAW                         : 0000000000000036
OWNERID                       : 131650					CLIENT_INFO                   :						P3TEXT                        :
TADDR                         :						FIXED_TABLE_SEQUENCE          : 3550139					P3                            : 23005726872
LOCKWAIT                      :						ROW_WAIT_OBJ#                 : -1					P3RAW                         : 000000055B3F8898
STATUS                        : ACTIVE					ROW_WAIT_FILE#                : 0					WAIT_CLASS_ID                 : 2723168908
SERVER                        : DEDICATED				ROW_WAIT_BLOCK#               : 0					WAIT_CLASS#                   : 6
SCHEMA#                       : 1481					ROW_WAIT_ROW#                 : 0					WAIT_CLASS                    : Idle
SCHEMANAME                    : xxxxxxxx				TOP_LEVEL_CALL#               : 59					WAIT_TIME                     : 0
OSUSER                        : odi					LOGON_TIME                    : 10-DEC-14				SECONDS_IN_WAIT               : 1
PROCESS                       : 32843					LAST_CALL_ET                  : 140					STATE                         : WAITING
MACHINE                       : xxxxxxxx				PDML_ENABLED                  : YES					WAIT_TIME_MICRO               : 574404
PORT                          : 42373					FAILOVER_TYPE                 : NONE					TIME_REMAINING_MICRO          : -1
TERMINAL                      :						FAILOVER_METHOD               : NONE					TIME_SINCE_LAST_WAIT_MICRO    : 0
PROGRAM                       : oracle@xxxxx.bis. (P015)		FAILED_OVER                   : NO					SERVICE_NAME                  : xxxx
TYPE                          : USER					RESOURCE_CONSUMER_GROUP       : xxx_GROUP				SQL_TRACE                     : DISABLED
SQL_ADDRESS                   : 00000003DF2E0A00			PDML_STATUS                   : ENABLED					SQL_TRACE_WAITS               : FALSE
SQL_HASH_VALUE                : 1379380956				PDDL_STATUS                   : ENABLED					SQL_TRACE_BINDS               : FALSE
SQL_ID                        : df2qwvt93gbqw				PQ_STATUS                     : ENABLED					SQL_TRACE_PLAN_STATS          : FIRST EXEC
SQL_CHILD_NUMBER              : 0					CURRENT_QUEUE_DURATION        : 0					SESSION_EDITION_ID            : 100
SQL_EXEC_START                : 10-DEC-14				CLIENT_IDENTIFIER             :						CREATOR_ADDR                  : 0000000548D60258
SQL_EXEC_ID                   : 33554432				BLOCKING_SESSION_STATUS       : UNKNOWN					CREATOR_SERIAL#               : 1
PREV_SQL_ADDR                 : 00					BLOCKING_INSTANCE             :						ECID                          :
PREV_HASH_VALUE               : 0					BLOCKING_SESSION              :						-----------------
PREV_SQL_ID                   :						FINAL_BLOCKING_SESSION_STATUS : UNKNOWN					PL/SQL procedure successfully completed.
PREV_CHILD_NUMBER             : 0					FINAL_BLOCKING_INSTANCE       :						Elapsed: 00:00:00.01
PREV_EXEC_START               :						FINAL_BLOCKING_SESSION        :
13:33:01 SYSTEM@NODS 10-DEC-14 SQL>      

 

 

I’ve copied the printtab.sql into printtabcol.sql and made little additions.

$ cat printtabcol.sql
@@saveset
set serverout on size 1000000

-- This is modified Tom Kyte's printtab code ( http://asktom.oracle.com )
set serverout on size 1000000

-- Modified part by FS
set term off
spool tmp/col.tmp

-- Tom Kyte's printtab code ( http://asktom.oracle.com )

declare
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    procedure execute_immediate( p_sql in varchar2 )
    is
    BEGIN
        dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
        l_status := dbms_sql.execute(l_theCursor);
    END;
begin
--    execute_immediate( 'alter session set nls_date_format=
--                        ''dd-mon-yyyy hh24:mi:ss'' ');
    dbms_sql.parse(  l_theCursor,
                     replace( '&1', '"', ''''),
                     dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor,
                               l_colCnt, l_descTbl );
    for i in 1 .. l_colCnt loop
        dbms_sql.define_column( l_theCursor, i,
                                l_columnValue, 4000 );
    end loop;
    l_status := dbms_sql.execute(l_theCursor);
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,
                                   l_columnValue );
            dbms_output.put_line
                ( rpad( l_descTbl(i).col_name,
                  30 ) || ': ' || l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
 --   execute_immediate( 'alter session set nls_date_format=
 --                          ''dd-MON-yy'' ');
exception
    when others then
        execute_immediate( 'alter session set
                         nls_date_format=''dd-MON-yy'' ');
        raise;
end;
/

-- Modified part by FS
spool off
host column  tmp/col.tmp
host rm tmp/col.tmp
set termout on
--
@@loadset
$ 

And the code to call is pretty simple:

$cat sesone.sql
@@printtabcol "select * from gv$session where inst_id=&1 and sid=&2"
set timing on
undef 1 2 3 4 5
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: