sqlplus -S / as sysdba <<EOF | tail -1 SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF SELECT ROUND((used.bytes / total.bytes) * 100, 2) FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files WHERE tablespace_name = UPPER('${TABLESPACE_NAME}') GROUP BY tablespace_name) total, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_segments WHERE tablespace_name = UPPER('${TABLESPACE_NAME}') GROUP BY tablespace_name) used WHERE total.tablespace_name = used.tablespace_name(+); EOF
case"$METRIC"in "sessions") sqlplus -S / as sysdba <<'EOF' | tail -1 SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF SELECT COUNT(*) FROM v\$session WHERE status = 'ACTIVE'; EOF ;; "archive_rate") sqlplus -S / as sysdba <<'EOF' | tail -1 SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF SELECT ROUND(SUM(blocks * block_size) / 1024 / 1024, 2) FROM v\$archived_log WHERE first_time > SYSDATE - 1/24 AND dest_id = 1; EOF ;; "dg_lag") sqlplus -S / as sysdba <<'EOF' | tail -1 SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF SELECT VALUE FROM v\$dataguard_stats WHERE NAME = 'apply lag'; EOF ;; esac
case"$OBJECT_TYPE"in "tablespace") sqlplus -S / as sysdba <<'EOF' SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF SELECT '{"data":[' FROM dual; SELECT LISTAGG('{"{#TABLESPACE_NAME}":"' || tablespace_name || '"}', ',') WITHIN GROUP (ORDER BY tablespace_name) FROM dba_tablespaces WHERE contents = 'PERMANENT'; SELECT ']}' FROM dual; EOF ;; esac
# 表空间使用率 [[metric]] context = "oracle_tablespace" metricsdesc = { pct_used = "Tablespace percent used", free_mb = "Free space in MB", total_mb = "Total space in MB" } request = """ SELECT t.tablespace_name, ROUND((1 - (f.free_bytes / t.total_bytes)) * 100, 2) as pct_used, ROUND(f.free_bytes / 1024 / 1024, 2) as free_mb, ROUND(t.total_bytes / 1024 / 1024, 2) as total_mb FROM (SELECT tablespace_name, SUM(bytes) total_bytes FROM dba_data_files GROUP BY tablespace_name) t, (SELECT tablespace_name, SUM(bytes) free_bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE t.tablespace_name = f.tablespace_name(+) """
# Active Session Count [[metric]] context = "oracle_session" metricsdesc = { active_count = "Number of active sessions", total_count = "Total sessions" } request = """ SELECT SUM(CASE WHEN status='ACTIVE' THEN 1 ELSE 0 END) as active_count, COUNT(*) as total_count FROM v$session WHERE type = 'USER' """
# DG Stats [[metric]] context = "oracle_dg" metricsdesc = { apply_lag_seconds = "Data Guard apply lag in seconds", transport_lag_seconds = "Transport lag in seconds" } request = """ SELECT EXTRACT(DAY FROM TO_DSINTERVAL(VALUE)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(VALUE)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(VALUE)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(VALUE)) as apply_lag_seconds FROM v$dataguard_stats WHERE name = 'apply lag' """
# Top SQL by Elapsed Time [[metric]] context = "oracle_top_sql" metricsdesc = { elapsed_seconds = "SQL elapsed time", executions = "SQL executions", buffer_gets = "Buffer gets" } request = """ SELECT ROWNUM as sql_rank, ROUND(elapsed_time / 1000000, 2) as elapsed_seconds, executions, buffer_gets FROM ( SELECT elapsed_time, executions, buffer_gets FROM v$sql ORDER BY elapsed_time DESC ) WHERE ROWNUM <= 10 """
配置 systemd 服务：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
# /etc/systemd/system/oracledb_exporter.service [Unit] Description=Oracle DB Exporter After=network.target