Oracle將結果豎向顯示

壹頁書發表於2015-04-23
小花狸監控設計之初,沒有考慮Golang連線Oracle特別麻煩.
但是如果不監控Oracle,感覺又是很大的缺失.
現在計劃採用Golang呼叫sqlplus命令的方式,採集資料.
但是sqlplus的結果怎麼採集和分析呢?
這裡面涉及很多格式的問題,也很麻煩.如果有類似mysql 查詢\G的方式就好了

可以由tom提供的print_table.sql指令碼進行改造
原指令碼如下:
create or replace procedure print_table
( p_query in varchar2,
  p_date_fmt in varchar2 default 'yyyy-mm-dd hh24:mi:ss' )


-- This utility is designedto be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBAs who use a role to gain access to the DBA_* views still work.
-- That is the purpose of AUTHID CURRENT_USER.
authid current_user
is
    l_thecursor     integer default dbms_sql.open_cursor;
    l_columnvalue   varchar2(4000);
    l_status        integer;
    l_desctbl       dbms_sql.desc_tab;
    l_colcnt        number;
    l_cs            varchar2(255);
    l_date_fmt      varchar2(255);


    -- Small inline procedure to restore the session's state.
    -- We may have modified the cursor sharing and nls date format
    -- session variables. This just restores them.
    procedure restore
    is
    begin
       if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
       then 
           execute immediate
           'alter session set cursor_sharing=exact';
       end if;
       if ( p_date_fmt is not null )
       then
           execute immediate
           'alter session set nls_date_format=''' || l_date_fmt || '''';
       end if;
       dbms_sql.close_cursor(l_thecursor);
    end restore;
begin
    -- I like to see the dates print out with times, by default. The
    -- format mask I use includes that.  In order to be "friendly"
    -- we save the current session's date format and then use
    -- the one with the date and time.  Passing in NULL will cause
    -- this routine just to use the current date format.
    if ( p_date_fmt is not null )
    then
       select sys_context( 'userenv', 'nls_date_format' )
         into l_date_fmt
         from dual;
       execute immediate
       'alter session set nls_date_format=''' || p_date_fmt || '''';
    end if;


    -- To be bind variable friendly on ad-hoc queries, we
    -- look to see if cursor sharing is already set to FORCE or
    -- similar. If not, set it to force so when we parse literals
    -- are replaced with binds.
    if ( dbms_utility.get_parameter_value
         ( 'cursor_sharing', l_status, l_cs ) = 1 )
    then
        if ( upper(l_cs) not in ('FORCE','SIMILAR'))
        then
            execute immediate
            'alter session set cursor_sharing=force';
        end if;
    end if;


    -- Parse and describe the query sent to us.  We need
    -- to know the number of columns and their names.
    dbms_sql.parse( l_thecursor, p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_thecursor, l_colcnt, l_desctbl );
    
    -- Define all columns to be cast to varchar2s. We
    -- are just printing them out.
    for i in 1 .. l_colcnt loop
        dbms_sql.define_column
        (l_thecursor, i, l_columnvalue, 4000);
    end loop;


    -- Execute the query, so we can fetch.
    l_status := dbms_sql.execute(l_thecursor);


    -- Loop and print out each column on a separate line.
    -- Bear in mind that dbms_output prints only 255 characters/line
    -- so we'll see only the first 200 characters by my design...
    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 )
              || ': ' ||
              substr( l_columnvalue, 1, 200 ) );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;


    -- Now, restore the session state, no matter what.
    restore;
exception 
    when others then
        restore;
        raise;
end;
/


呼叫指令碼
#!/bin/bash
sqlplus -s / as sysdba < set serveroutput on;
call print_table('
SELECT 
D.TABLESPACE_NAME, 
SPACE  AS "SUM_SPACE(M)", 
SPACE - NVL(FREE_SPACE, 0)  AS "USED_SPACE(M)", 
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2)  AS "USED_RATE(%)", 
FREE_SPACE  AS "FREE_SPACE(M)"
FROM (
    SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) AS SPACE, SUM(BLOCKS) AS BLOCKS
    FROM DBA_DATA_FILES
    GROUP BY TABLESPACE_NAME
) D
    INNER JOIN (
        SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) AS FREE_SPACE
        FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME
    ) F ON D.TABLESPACE_NAME = F.TABLESPACE_NAME 
order by 1
');
exit
!

結果:


透過這種方式,計劃監控oracle的表空間使用率,閃回區使用率和DataGuard資訊.

喵星神將

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29254281/viewspace-1593330/,如需轉載,請註明出處,否則將追究法律責任。

相關文章