Oracle將結果豎向顯示
小花狸監控設計之初,沒有考慮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資訊.
喵星神將
但是如果不監控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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL中如何橫向顯示結果集薦MySql
- oracle縱向顯示變為橫向顯示Oracle
- 輸出顯示豎指令碼指令碼
- Oracle 11g RAC crs_stat 命令結果完整顯示Oracle
- 用sh列表顯示oracle資料庫單條查詢結果(轉)Oracle資料庫
- Golang pprof 結果使用 graphviz 圖片化顯示Golang
- 如何分頁顯示資料庫查詢結果?資料庫
- MySQL pager和nopager命令--不顯示查詢結果MySql
- Google良心!搜尋結果右側不再顯示廣告Go
- Win10如何將音量條從橫向變為豎向 win10音量條怎麼從橫向變為豎向Win10
- SAP Hybris裡搜尋顯示結果的實現原理
- 【Shell】grep命令在結果集中顯示綠色的字元字元
- V$SQL檢視顯示結果異常的診斷SQL
- 關於把豎向單個佈局在鴻蒙等摺疊手機屏中顯示成雙向佈局鴻蒙
- chat-gpt-google-extension: 在谷歌搜尋結果中同時顯示ChatGPT結果的chrome外掛Go谷歌ChatGPTChrome
- Oracle資料庫PL/SQL Developer查詢結果顯示問號亂碼的解決方法Oracle資料庫SQLDeveloper
- 慢動作輸出 Linux 命令結果並用彩色顯示Linux
- 如何讓crs_stat命令結果中的name列完全顯示
- 谷歌TPU2代有望取代英偉達GPU?測評結果顯示…谷歌GPU
- Windows10 Cortana搜尋結果顯示緩慢怎麼辦?Windows
- ES查詢之查詢屬性過濾、結果高亮顯示
- MongoDB 顯示數量方式 , count結果與實際不符合MongoDB
- MySQL 查詢結果以百分比顯示簡單實現MySql
- VS2015 查詢結果明細沒顯示問題
- CSS實現文字豎排顯示(相容IE6/IE7)CSS
- relink oracle的結果Oracle
- 『動善時』JMeter基礎 — 33、JMeter察看結果樹的顯示模式詳解JMeter模式
- HANA Studio中修改預設查詢結果只顯示1000行
- 分割結果視覺化,把標籤mask輪廓顯示在原圖上視覺化
- gdb顯示結構體結構體
- oracle中文顯示亂碼Oracle
- 為什麼這段程式碼輸入一個數之後顯示不了結果
- sqlplus格式化輸出(輸出結果顯示在同一行)SQL
- Ruckus:調查結果顯示卓越企業能夠快速接受CRM關鍵技術
- 89n:調查結果顯示Google+每日發帖數量開始下滑Go
- 利用MySQL的一個特性實現MySQL查詢結果的分頁顯示(轉)MySql
- 物件導向-程式碼塊看程式寫結果物件
- Win10系統搜狗輸入法怎麼設定豎排顯示Win10