Tag Archives: Oracle

Oracle query: table size in bytes

SELECT TRUNC(sum(bytes)) table_size, table_name 
FROM (SELECT segment_name table_name, bytes
    FROM user_segments
    WHERE segment_type = 'TABLE'
    UNION ALL
    SELECT i.table_name, s.bytes
    FROM user_indexes i, user_segments s
    WHERE s.segment_name = i.index_name 
    AND   s.segment_type = 'INDEX'
    UNION ALL
    SELECT l.table_name, s.bytes
    FROM user_lobs l, user_segments s
    WHERE s.segment_name = l.segment_name 
    AND   s.segment_type = 'LOBSEGMENT'
    UNION ALL
    SELECT l.table_name, s.bytes
    FROM user_lobs l, user_segments s
    WHERE s.segment_name = l.index_name 
    AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name
HAVING SUM(bytes)/1024/1024 > 100

DataDog Oracle custom_query: Only float values can be submitted as metrics

Errors in log

2021-12-30 13:07:02 UTC | CORE | WARN | (pkg/collector/python/datadog_agent.go:124 in LogMessage) | oracle:9eb61efa95cfcc8c | (base.py:580) | Metric: 'test.table_rows.rowcount' has non float value: 'ITEMS'. Only float values can be submitted as metrics.

Configuration with issue

instances:
  - custom_queries:
    - metric_prefix: test.table_rows        
      query: select table_name, regexp_replace(to_char(nvl(num_rows,-1), '99999999999.9'),'
            ', '') rowcount from all_tables where owner='TEST'
      columns:
        - name: rowcount
          nype: gauge
        - name: table_name
          type: tag

Solution:

Set first field as field with value:

select (...) rowcount, table_name from ...