Add allocated space metrics to oracledb charts (#10197)

Co-authored-by: Ilya Mashchenko <ilya@netdata.cloud>

2 files changed, 122 insertions, 0 deletions

diff --git a/collectors/python.d.plugin/oracledb/README.md b/collectors/python.d.plugin/oracledb/README.md
index dbe6505e63..3f536c8b24 100644
--- a/collectors/python.d.plugin/oracledb/README.md
+++ b/collectors/python.d.plugin/oracledb/README.md
@@ -41,6 +41,10 @@ It produces following charts:
     -   Size
     -   Usage
     -   Usage In Percent
+-   allocated space
+    -   Size
+    -   Usage
+    -   Usage In Percent
 
 ## prerequisite
 
diff --git a/collectors/python.d.plugin/oracledb/oracledb.chart.py b/collectors/python.d.plugin/oracledb/oracledb.chart.py
index c56dcb5f8b..28ef8db106 100644
--- a/collectors/python.d.plugin/oracledb/oracledb.chart.py
+++ b/collectors/python.d.plugin/oracledb/oracledb.chart.py
@@ -34,6 +34,9 @@ ORDER = [
     'tablespace_size',
     'tablespace_usage',
     'tablespace_usage_in_percent',
+    'allocated_size',
+    'allocated_usage',
+    'allocated_usage_in_percent',
 ]
 
 CHARTS = {
@@ -170,6 +173,18 @@ CHARTS = {
         'options': [None, 'Usage', '%', 'tablespace', 'oracledb.tablespace_usage_in_percent', 'line'],
         'lines': [],
     },
+    'allocated_size': {
+        'options': [None, 'Size', 'B', 'tablespace', 'oracledb.allocated_size', 'line'],
+        'lines': [],
+    },
+    'allocated_usage': {
+        'options': [None, 'Usage', 'B', 'tablespace', 'oracledb.allocated_usage', 'line'],
+        'lines': [],
+    },
+    'allocated_usage_in_percent': {
+        'options': [None, 'Usage', '%', 'tablespace', 'oracledb.allocated_usage_in_percent', 'line'],
+        'lines': [],
+    },
 }
 
 CX_CONNECT_STRING = "{0}/{1}@//{2}/{3}"
@@ -193,6 +208,27 @@ FROM
   dba_tablespace_usage_metrics m
   JOIN dba_tablespaces t ON m.tablespace_name = t.tablespace_name
 '''
+QUERY_ALLOCATED = '''
+SELECT
+  nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) tablespace_name,
+  bytes_alloc used_bytes,
+  bytes_alloc-nvl(bytes_free,0) max_bytes,
+  ((bytes_alloc-nvl(bytes_free,0))/ bytes_alloc)*100 used_percent
+FROM
+  (SELECT
+    sum(bytes) bytes_free,
+    tablespace_name
+    FROM sys.dba_free_space
+    GROUP BY tablespace_name
+  ) a,
+  (SELECT
+    sum(bytes) bytes_alloc,
+    tablespace_name
+    FROM sys.dba_data_files
+    GROUP BY tablespace_name
+  ) b
+WHERE a.tablespace_name (+) = b.tablespace_name
+'''
 QUERY_ACTIVITIES_COUNT = '''
 SELECT
   name,
@@ -397,6 +433,26 @@ class Service(SimpleService):
                 data['{0}_tablespace_used'.format(name)] = int(used * 1000)
                 data['{0}_tablespace_used_in_percent'.format(name)] = int(used_in_percent * 1000)
 
+        # ALLOCATED SPACE
+        try:
+            rv = self.gather_allocated_metrics()
+        except cx_Oracle.Error as error:
+            self.error(error)
+            self.alive = False
+            return None
+        else:
+            for name, offline, size, used, used_in_percent in rv:
+                # TODO: skip offline?
+                if not (not offline and self.charts):
+                    continue
+                # TODO: remove inactive?
+                if name not in self.active_tablespaces:
+                    self.active_tablespaces.add(name)
+                    self.add_tablespace_to_charts(name)
+                data['{0}_allocated_size'.format(name)] = int(size * 1000)
+                data['{0}_allocated_used'.format(name)] = int(used * 1000)
+                data['{0}_allocated_used_in_percent'.format(name)] = int(used_in_percent * 1000)
+
         return data or None
 
     def gather_system_metrics(self):
@@ -612,6 +668,44 @@ class Service(SimpleService):
                 )
         return metrics
 
+    def gather_allocated_metrics(self):
+        """
+        :return:
+
+        [['SYSTEM', 874250240.0, 3233169408.0, 27.040038107400033, 0],
+         ['SYSAUX', 498860032.0, 3233169408.0, 15.429443033997678, 0],
+         ['TEMP', 0.0, 3233177600.0, 0.0, 0],
+         ['USERS', 1048576.0, 3233169408.0, 0.03243182981397305, 0]]
+        """
+        metrics = list()
+        with self.conn.cursor() as cursor:
+            cursor.execute(QUERY_ALLOCATED)
+            for tablespace_name, used_bytes, max_bytes, used_percent in cursor.fetchall():
+                if used_bytes is None:
+                    offline = True
+                    used = 0
+                else:
+                    offline = False
+                    used = float(used_bytes)
+                if max_bytes is None:
+                    size = 0
+                else:
+                    size = float(max_bytes)
+                if used_percent is None:
+                    used_percent = 0
+                else:
+                    used_percent = float(used_percent)
+                metrics.append(
+                    [
+                        tablespace_name,
+                        offline,
+                        size,
+                        used,
+                        used_percent,
+                    ]
+                )
+        return metrics
+
     def gather_wait_time_metrics(self):
         """
         :return:
@@ -711,3 +805,27 @@ class Service(SimpleService):
                 1,
                 1000,
             ])
+        self.charts['allocated_size'].add_dimension(
+            [
+                '{0}_allocated_size'.format(name),
+                name,
+                'absolute',
+                1,
+                1000,
+                ])
+        self.charts['allocated_usage'].add_dimension(
+            [
+                '{0}_allocated_used'.format(name),
+                name,
+                'absolute',
+                1,
+                1000,
+                ])
+        self.charts['allocated_usage_in_percent'].add_dimension(
+            [
+                '{0}_allocated_used_in_percent'.format(name),
+                name,
+                'absolute',
+                1,
+                1000,
+            ])