Munin contributed plugins

#!/bin/bash

# -*- sh -*-

: << =cut

=head1 NAME

  oracle_sysstat_* - Munin plugin to monitor Oracle Statistics

    execute              - To monitor Oracle Sysstat Execute Count

    parse                - To monitor Oracle Sysstat Parse Count

    tablefetch           - To monitor Oracle Sysstat Table Fetch Rows

    tablescan            - To monitor Oracle Sysstat Table Scans

    transaction          - To monitor Oracle Sysstat Transactions

    sort                 - To monitor Oracle Sysstat Sorts

    cursor               - To monitor Oracle Sysstat Open Cursor

    enqueue              - To monitor Oracle Sysstat Enqueues

    redolog              - To monitor Oracle Sysstat Redo Log

    physicaliops         - To monitor Oracle Sysstat Physical I/O Requests

    physicalrw           - To monitor Oracle Sysstat Physical Read/Write Bytes

    netrw                - To monitor Oracle Sysstat Network Send/Receive Bytes

    sgainfo              - To monitor Oracle Memory SGA

    pgastat              - To monitor Oracle Memory PGA

    cachehit             - To monitor Oracle Cache Hit Ratio

    sessionuser          - To monitor Oracle Session Users

    sessionwait          - To monitor Oracle Session Wait

    eventwait            - To monitor Oracle Wait Events

    eventwaitapplication - To monitor Oracle Wait Events Application

    eventwaitconcurrency - To monitor Oracle Wait Events Concurrency

    eventwaituserio      - To monitor Oracle Wait Events User I/O

    eventwaitsystemio    - To monitor Oracle Wait Events System I/O

    eventwaitcluster     - To monitor Oracle Wait Events Cluster

    tablespace           - To monitor Oracle Table Space Usage

    asmusage             - To monitor Oracle ASM Disk Group Usage

=head1 CONFIGURATION

  To get a list of symlinks that can be created, run:   

    ./oracle_sysstat_ suggest

  Make symlinks:

    munin-node-configure --families=contrib --suggest --shell

    ...

  The following shows example settings for this plugin:

    [oracle_sysstat_*]

      user  oracle

      env.oracle_auth  / as SYSDBA

      env.ORACLE_HOME  /path/to/oracle/product/version

      env.ORACLE_SID   SOMESID

    [oracle_sysstat_asmusage]

      user  grid

      env.oracle_auth  / as SYSASM

      env.ORACLE_HOME  /path/to/grid/home/version

      env.ORACLE_SID   SOMESID

=head1 NOTES

  Uses the command "sqlplus".

  Tested with Oracle Database 12c R1.

=head1 AUTHOR

  K.Cima https://github.com/shakemid

=head1 LICENSE

  GPLv2

=cut

# Magic markers

#%# family=contrib

#%# capabilities=autoconf suggest

# Include plugin.sh

. "${MUNIN_LIBDIR}/plugins/plugin.sh"

# Like perl 'use strict;' 

#set -o nounset

# Environments

: "${ORACLE_HOME:=$( echo /opt/oracle/product/* )}"

: "${ORACLE_SID:=orcl}"

: "${oracle_auth:=/ as SYSDBA}"

PATH=$PATH:$ORACLE_HOME/bin

export PATH ORACLE_HOME ORACLE_SID

# Module name

module=$( basename "$0" | sed -e 's/^.*_//' )

# Graph settings

declare -A global_attrs  # required

declare -A data_attrs    # required (format: field type draw 'label')

declare -A getfield_func # optional

declare -A getvalue_func # required

key=execute

global_attrs[$key]="

    graph_title Oracle Sysstat Execute Count

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count per second

    graph_info Oracle Sysstat Execute Count

"

data_attrs[$key]="

    execute_count    DERIVE LINE 'execute count'

    user_calls       DERIVE LINE 'user calls'

    recursive_calls  DERIVE LINE 'recursive calls'

"

getvalue_func[$key]=getvalue_sysstat

key=parse

global_attrs[$key]="

    graph_title Oracle Sysstat Parse Count

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count per second

    graph_info Oracle Sysstat Parse Count

"

data_attrs[$key]="

    parse_count_total     DERIVE LINE 'parse count (total)'

    parse_count_hard      DERIVE LINE 'parse count (hard)'

    parse_count_failures  DERIVE LINE 'parse count (failures)'

    parse_count_describe  DERIVE LINE 'parse count (describe)'

"

getvalue_func[$key]=getvalue_sysstat

key=tablefetch

global_attrs[$key]="

    graph_title Oracle Sysstat Table Fetch Rows

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count per second

    graph_info Oracle Sysstat Table Scans

"

data_attrs[$key]="

    table_fetch_by_rowid   DERIVE LINE 'table fetch by rowid'

    table_scan_rows_gotten DERIVE LINE 'table scan rows gotten'

"

getvalue_func[$key]=getvalue_sysstat

key=tablescan

global_attrs[$key]="

    graph_title Oracle Sysstat Table Scans

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count per second

    graph_info Oracle Sysstat Table Scans

"

data_attrs[$key]="

    table_scans_short_tables DERIVE LINE 'table scans (short tables)'

    table_scans_long_tables  DERIVE LINE 'table scans (long tables)'

"

getvalue_func[$key]=getvalue_sysstat

key=transaction

global_attrs[$key]="

    graph_title Oracle Sysstat Transactions

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count per second

    graph_info Oracle Sysstat Transactions

"

data_attrs[$key]="

    user_commits   DERIVE LINE 'user commits'

    user_rollbacks DERIVE LINE 'user rollbacks'

"

getvalue_func[$key]=getvalue_sysstat

key=sort

global_attrs[$key]="

    graph_title Oracle Sysstat Sorts

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count per second

    graph_info Oracle Sysstat - Sorts

"

data_attrs[$key]="

    sorts_memory DERIVE LINE 'sorts (memory)'

    sorts_disk   DERIVE LINE 'sorts (disk)'

"

getvalue_func[$key]=getvalue_sysstat

key=cursor

global_attrs[$key]="

    graph_title Oracle Sysstat Open Cursors

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count

    graph_info Oracle Sysstat Open Cursors

"

data_attrs[$key]="

    open_cursor GAUGE LINE 'opened cursors current'

"

getvalue_func[$key]=getvalue_sysstat

key=enqueue

global_attrs[$key]="

    graph_title Oracle Sysstat Enqueues

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count per second

    graph_info Oracle Sysstat Enqueues

"

data_attrs[$key]="

    enqueue_requests    DERIVE LINE 'enqueue requests'

    enqueue_releases    DERIVE LINE 'enqueue releases'

    enqueue_conversions DERIVE LINE 'enqueue conversions'

    enqueue_waits       DERIVE LINE 'enqueue waits'

    enqueue_timeouts    DERIVE LINE 'enqueue timeouts'

    enqueue_deadlocks   DERIVE LINE 'enqueue deadlocks'

"

getvalue_func[$key]=getvalue_sysstat

key=redolog

global_attrs[$key]="

    graph_title Oracle Sysstat Redo Log

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count per second

    graph_info Oracle Sysstat Redo Log

"

data_attrs[$key]="

    redo_entries                   DERIVE LINE 'redo entries'

    redo_buffer_allocation_retries DERIVE LINE 'redo buffer allocation retries'

    redo_log_space_requests        DERIVE LINE 'redo log space requests'

"

getvalue_func[$key]=getvalue_sysstat

key=physicaliops

global_attrs[$key]="

    graph_title Oracle Sysstat Physical I/O Requests

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel iops

    graph_info Oracle Sysstat Physical I/O Requests

"

data_attrs[$key]="

    physical_read_total        DERIVE LINE2 'physical read total IO requests'

    physical_read              DERIVE LINE  'physical read IO requests'

    physical_read_total_multi  DERIVE LINE  'physical read total multi block requests'

    physical_write_total       DERIVE LINE2 'physical write total IO requests'

    physical_write             DERIVE LINE  'physical write IO requests'

    physical_write_total_multi DERIVE LINE  'physical write total multi block requests'

"

getvalue_func[$key]=getvalue_sysstat

key=physicalrw

global_attrs[$key]="

    graph_title Oracle Sysstat Physical Read/Write Bytes

    graph_category db

    graph_args --base 1024 --lower-limit 0 --rigid

    graph_vlabel bytes per second

    graph_info Oracle Sysstat Physical Read/Write Bytes

"

data_attrs[$key]="

    physical_read_total  DERIVE LINE2 'physical read total bytes'

    physical_read        DERIVE LINE  'physical read bytes'

    physical_write_total DERIVE LINE2 'physical write total bytes'

    physical_write       DERIVE LINE  'physical write bytes'

"

getvalue_func[$key]=getvalue_sysstat

key=netrw

global_attrs[$key]="

    graph_title Oracle Sysstat Network Send/Receive Bytes

    graph_category db

    graph_args --base 1024 --lower-limit 0 --rigid

    graph_vlabel bytes per second

    graph_info Oracle Sysstat Network Send/Receive Bytes

"

data_attrs[$key]="

    bytes_sent_via_sql_net_to_client       DERIVE LINE 'bytes sent via SQL*Net to client'

    bytes_received_via_sql_net_from_client DERIVE LINE 'bytes received via SQL*Net from client'

    bytes_sent_via_sql_net_to_dblink       DERIVE LINE 'bytes sent via SQL*Net to dblink'

    bytes_received_via_sql_net_from_dblink DERIVE LINE 'bytes received via SQL*Net from dblink'

"

getvalue_func[$key]=getvalue_sysstat

key=sgainfo

global_attrs[$key]="

    graph_title Oracle Memory SGA

    graph_category db

    graph_args --base 1024 --lower-limit 0 --rigid

    graph_vlabel bytes

    graph_info Oracle Memory SGA

"

data_attrs[$key]="

    maximum_sga_size    GAUGE LINE      'Maximum SGA Size'

    fixed_sga_size      GAUGE AREASTACK 'Fixed SGA Size'

    redo_buffers        GAUGE AREASTACK 'Redo Buffers'

    shared_pool_size    GAUGE AREASTACK 'Shared Pool Size'

    large_pool_size     GAUGE AREASTACK 'Large Pool Size'

    java_pool_size      GAUGE AREASTACK 'Java Pool Size'

    streams_pool_size   GAUGE AREASTACK 'Streams Pool Size'

    shared_io_pool_size GAUGE AREASTACK 'Shared IO Pool Size'

    buffer_cache_size   GAUGE AREASTACK 'Buffer Cache Size'

    in_memory_area_size GAUGE AREASTACK 'In-Memory Area Size'

"

getvalue_func[$key]=getvalue_sgainfo

key=pgastat

global_attrs[$key]="

    graph_title Oracle Memory PGA

    graph_category db

    graph_args --base 1024 --lower-limit 0 --rigid

    graph_vlabel bytes

    graph_info Oracle Memory PGA

"

data_attrs[$key]="

    pga_target    GAUGE LINE 'aggregate PGA auto target'

    pga_allocated GAUGE LINE 'total PGA allocated'

    pga_inuse     GAUGE AREA 'total PGA inuse'

"

getvalue_func[$key]=getvalue_pgastat

key=cachehit

global_attrs[$key]="

    graph_title Oracle Cache Hit Ratio

    graph_category db

    graph_args --base 1000 --lower-limit 0 --upper-limit 100 --rigid

    graph_vlabel %

    graph_info Oracle Cache Hit Ratio

    graph_scale no

"

data_attrs[$key]="

    buf_hitratio  GAUGE LINE 'Buffer Cache Hit Ratio'

    lib_hitratio  GAUGE LINE 'Library Cache Hit Ratio'

    dict_hitratio GAUGE LINE 'Dictionary Cache Hit Ratio'

"

getvalue_func[$key]=getvalue_cachehit

key=sessionuser

global_attrs[$key]="

    graph_title Oracle Session Users

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count

    graph_info Oracle Session Users

"

data_attrs[$key]=""

getfield_func[$key]=getfield_sessionuser

getvalue_func[$key]=getvalue_sessionuser

key=sessionwait

global_attrs[$key]="

    graph_title Oracle Session Wait

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel count

    graph_info Oracle Session Wait

"

data_attrs[$key]=""

getfield_func[$key]=getfield_sessionwait

getvalue_func[$key]=getvalue_sessionwait

key=eventwait

global_attrs[$key]="

    graph_title Oracle Wait Events

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel microseconds

    graph_info Oracle Wait Events

"

data_attrs[$key]=""

getfield_func[$key]=getfield_eventwait

getvalue_func[$key]=getvalue_eventwait

key=eventwaitapplication

global_attrs[$key]="

    graph_title Oracle Wait Events Application

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel microseconds

    graph_info Oracle Wait Events Application

"

data_attrs[$key]=""

getfield_func[$key]='getfield_eventwait2 Application'

getvalue_func[$key]='getvalue_eventwait2 Application'

key=eventwaitconcurrency

global_attrs[$key]="

    graph_title Oracle Wait Events Concurrency

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel microseconds

    graph_info Oracle Wait Events Concurrency

"

data_attrs[$key]=" "

getfield_func[$key]='getfield_eventwait2 Concurrency'

getvalue_func[$key]='getvalue_eventwait2 Concurrency'

key=eventwaituserio

global_attrs[$key]="

    graph_title Oracle Wait Events User I/O

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel microseconds

    graph_info Oracle Wait Events User I/O

"

data_attrs[$key]=""

getfield_func[$key]='getfield_eventwait2 "User I/O"'

getvalue_func[$key]='getvalue_eventwait2 "User I/O"'

key=eventwaitsystemio

global_attrs[$key]="

    graph_title Oracle Wait Events System I/O

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel microseconds

    graph_info Oracle Wait Events System I/O

"

data_attrs[$key]="

"

getfield_func[$key]='getfield_eventwait2 "System I/O"'

getvalue_func[$key]='getvalue_eventwait2 "System I/O"'

key=eventwaitcluster

global_attrs[$key]="

    graph_title Oracle Wait Events Cluster

    graph_category db

    graph_args --base 1000 --lower-limit 0 --rigid

    graph_vlabel microseconds

    graph_info Oracle Wait Events Cluster

"

data_attrs[$key]=" "

getfield_func[$key]='getfield_eventwait2 Cluster'

getvalue_func[$key]='getvalue_eventwait2 Cluster'

key=tablespace

global_attrs[$key]="

    graph_title Oracle Table Space Usage

    graph_category db

    graph_args --base 1000 --lower-limit 0 --upper-limit 100 --rigid

    graph_vlabel %

    graph_info Oracle Table Space Usage

    warning ${warning:=92}

    critical ${critical:=98}

"

data_attrs[$key]=""

getfield_func[$key]=getfield_tablespace

getvalue_func[$key]=getvalue_tablespace

key=asmusage

global_attrs[$key]="

    graph_title Oracle ASM Disk Group Usage

    graph_category db

    graph_args --base 1000 --lower-limit 0 --upper-limit 100 --rigid

    graph_vlabel %

    graph_info Oracle ASM Disk Group Usage

    warning ${warning:=92}

    critical ${critical:=98}

"

data_attrs[$key]=""

getfield_func[$key]=getfield_asmusage

getvalue_func[$key]=getvalue_asmusage

# End of Graph Settings

# sqlplus options

: "${sqlplus:=sqlplus -S -L}"

sqlplus_variables="

    set pagesize 0

    set feed off

    set head off

    set linesize 256

    set numwidth 20

"

# functions 

autoconf() {

    if [ -x "$( which "${sqlplus}" )" ]; then

        echo yes

    else

        echo "no (failed to find executable 'sqlplus')"

    fi

}

suggest() {

    echo "${!global_attrs[@]}" | tr ' ' '\n' | sort

}

config() {

    # print global attributes

    sed -e 's/^  *//' -e '/^$/d' <<< "${global_attrs[$module]}"

    # print data source attributes

    local line t fields field type draw label

    while IFS= read -r line

    do

        eval 't=(' "$line" ')'

        field="${t[0]}"

        [ -z "$field" ] && continue

        fields+=( $field )

        type="${t[1]}"

        draw="${t[2]}"

        label="${t[3]}"

        echo "${field}.type"  "$type"

        echo "${field}.draw"  "$draw"

        echo "${field}.label" "$label"

    done <<< "${data_attrs[$module]}"

    echo graph_order "${fields[@]}"

}

getfield() {

    if [ -n "${getfield_func[$module]:-}" ]; then

        eval "${getfield_func[$module]}"

    fi

}

getvalue() {

    eval "${getvalue_func[$module]}"

}

getvalue_sysstat() {

    local line t field label

    while IFS= read -r line

    do

        eval 't=(' "$line" ')'

        field="${t[0]}"

        [ -z "$field" ] && continue

        label="${t[3]}"

        ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

VAR vf VARCHAR2(64)

VAR vl VARCHAR2(64)

EXEC :vf := '${field}' 

EXEC :vl := '${label}' 

SELECT

  :vf || '.value ' || value

FROM

  v\$sysstat

WHERE

  name = :vl;

EOF

    done <<< "${data_attrs[$module]}"

}

getvalue_sgainfo() {

    local line t field label

    while IFS= read -r line

    do

        eval 't=(' "$line" ')'

        field="${t[0]}"

        [ -z "$field" ] && continue

        label="${t[3]}"

        ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

VAR vf VARCHAR2(64)

VAR vl VARCHAR2(64)

EXEC :vf := '${field}' 

EXEC :vl := '${label}' 

SELECT

  :vf || '.value ' || bytes

FROM

  v\$sgainfo

WHERE

  name = :vl;

EOF

    done <<< "${data_attrs[$module]}"

}

getvalue_pgastat() {

    local line t field label

    while IFS= read -r line

    do

        eval 't=(' "$line" ')'

        field="${t[0]}"

        [ -z "$field" ] && continue

        label="${t[3]}"

        ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

VAR vf VARCHAR2(64)

VAR vl VARCHAR2(64)

EXEC :vf := '${field}' 

EXEC :vl := '${label}' 

SELECT

  :vf || '.value ' || value

FROM

  v\$pgastat

WHERE

  name = :vl;

EOF

    done <<< "${data_attrs[$module]}"

}

getvalue_cachehit() {

    ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  'buf_hitratio.value ' || ROUND( ( 1 - a.value / ( b.value + c.value ) ) * 100 )

FROM

  v\$sysstat a, v\$sysstat b, v\$sysstat c

WHERE

  a.name = 'physical reads' AND

  b.name = 'db block gets' AND

  c.name = 'consistent gets'

;

SELECT

  'lib_hitratio.value ' || ROUND( SUM(pins) / ( SUM(pins) + SUM(reloads) ) * 100 )

FROM

  v\$librarycache

;

SELECT

  'dict_hitratio.value ' || ROUND( ( 1 - SUM(getmisses) / SUM(gets) ) * 100 )

FROM

  v\$rowcache

;

EOF

}

getfield_sessionuser() {

    data_attrs[$module]="$( ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( username, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||

  ' GAUGE LINE ' || '''' || username || ''''

FROM

  dba_users

WHERE

  account_status = 'OPEN'

ORDER BY

  username;

EOF

)"

}

getvalue_sessionuser() {

    ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( du.username, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||

  count(vs.username)

FROM 

  ( SELECT

      username

    FROM

      dba_users

    WHERE

      account_status = 'OPEN'

  ) du

  LEFT JOIN v\$session vs

ON

  du.username = vs.username

GROUP BY

  du.username;

EOF

}

getfield_sessionwait() {

    data_attrs[$module]="$( ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( wait_class, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||

  ' GAUGE AREASTACK ' || '''' || wait_class || ''''

FROM

  v\$event_name

WHERE

  wait_class NOT IN ( 'Other', 'Idle' )

GROUP BY

  wait_class

ORDER BY

  wait_class;

SELECT 'Other GAUGE AREASTACK Other' from dual;

SELECT 'Idle  GAUGE AREASTACK Idle' from dual;

EOF

)"

}

getvalue_sessionwait() {

    ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( en.wait_class, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||

  count(se.wait_class)

FROM

  ( SELECT

      wait_class

    FROM

      v\$event_name

    GROUP BY

      wait_class

  ) en

  LEFT JOIN v\$session se

ON

  en.wait_class = se.wait_class AND 

  se.username is not null

GROUP BY

  en.wait_class;

EOF

}

getfield_eventwait() {

    data_attrs[$module]="$( ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( wait_class, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||

  ' DERIVE LINE ' || '''' || wait_class || ''''

FROM

  v\$event_name

WHERE

  wait_class NOT IN ( 'Other', 'Idle' )

GROUP BY

  wait_class

ORDER BY

  wait_class;

SELECT 'Other DERIVE LINE Other' from dual;

EOF

)"

}

getvalue_eventwait() {

    ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( en.wait_class, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||

  NVL( SUM(se.time_waited_micro), 0 )

FROM

  ( SELECT

      wait_class

    FROM

      v\$event_name

    WHERE

      wait_class NOT IN ( 'Idle' )

    GROUP BY

      wait_class

  ) en

  LEFT JOIN v\$system_event se

ON

  en.wait_class = se.wait_class

GROUP BY

  en.wait_class;

EOF

}

getfield_eventwait2() {

    local waitclass="$1"

    data_attrs[$module]="$( ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

VAR vl VARCHAR2(64)

EXEC :vl := '${waitclass}' 

SELECT

  REGEXP_REPLACE( name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||

  ' DERIVE LINE ' || '''' || SUBSTR( name, 1, 45 ) || ''''

FROM

  v\$event_name 

WHERE

  wait_class = :vl

ORDER BY

  name;

EOF

)"

}

getvalue_eventwait2() {

    local waitclass="$1"

    ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

VAR vl VARCHAR2(64)

EXEC :vl := '${waitclass}' 

SELECT

  REGEXP_REPLACE( en.name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' || 

  NVL( se.time_waited_micro, 0 )

FROM

  v\$event_name en LEFT JOIN v\$system_event se

ON

  en.name = se.event

WHERE

  en.wait_class = :vl;

EOF

}

getfield_tablespace() {

    data_attrs[$module]="$( ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( tablespace_name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||

  ' GAUGE LINE ' || '''' || tablespace_name || ''''

FROM

  dba_data_files

ORDER BY

  tablespace_name;

EOF

)"

}

getvalue_tablespace() {

    ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( tablespace_name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||

  ROUND( (total_bytes - free_total_bytes) / total_bytes * 100, 0 )

FROM

  ( SELECT

      tablespace_name,

      SUM(bytes) total_bytes

    FROM

      dba_data_files

    GROUP BY

      tablespace_name

  ),

  ( SELECT

      tablespace_name free_tablespace_name,

      SUM(bytes) free_total_bytes

    FROM

      dba_free_space

    GROUP BY

      tablespace_name

  )

WHERE

  tablespace_name = free_tablespace_name;

EOF

}

getfield_asmusage() {

    data_attrs[$module]="$( ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||

  ' GAUGE LINE ' || '''' || name || ''''

FROM

  v\$asm_diskgroup

ORDER BY

  name;

EOF

)"

}

getvalue_asmusage() {

    ${sqlplus} "${oracle_auth}" <<EOF

${sqlplus_variables}

SELECT

  REGEXP_REPLACE( name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||

  ROUND( ( total_mb - free_mb ) / total_mb * 100 )

FROM

  v\$asm_diskgroup

ORDER BY

  name;

EOF

}

# main

case ${1:-} in

autoconf)

    autoconf

    ;;

suggest)

    suggest

    ;;

config)

    getfield

    config

    ;;

*)

    getvalue

    ;;

esac

exit 0