Oracle RDS Monitoring beyond free space

Monitoring RDS instances, including Oracle, goes beyond just checking for free space. Amazon CloudWatch provides a wide range of metrics for monitoring your RDS instances. These metrics include CPU utilization, database connections, read and write operations, and many others.

For monitoring tablespace utilization specifically, you can enable Enhanced Monitoring for your RDS instances. Enhanced Monitoring provides metrics in real time for the operating system that your DB instance runs on. You can view key operational metrics for your DB instance load, including active sessions, I/O, and system metrics, which can help you analyze and troubleshoot your database performance.

However, please note that Enhanced Monitoring doesn't provide tablespace utilization out of the box. Oracle tablespaces are specific to Oracle databases and aren't directly exposed as a metric in RDS or CloudWatch.

For tablespace-related metrics, you will have to query the Oracle system views directly. You can create a procedure in Oracle to gather this information and then use RDS Events or a Lambda function to trigger a custom CloudWatch metric.

SELECT 
   df.tablespace_name "Tablespace",
   totalusedspace "Used MB",
   (df.totalspace - tu.totalusedspace) "Free MB",
   df.totalspace "Total MB",
   ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Percent Free"
FROM
   (SELECT 
      tablespace_name,
      ROUND(SUM(bytes) / 1048576) TotalSpace
    FROM 
      dba_data_files
    GROUP BY 
      tablespace_name) df,
   (SELECT 
      ROUND(SUM(bytes)/(1024*1024)) totalusedspace, tablespace_name
    FROM 
      dba_segments
    GROUP BY 
      tablespace_name) tu
WHERE
   df.tablespace_name = tu.tablespace_name;

In AWS RDS, where we create BIGFILE tablespaces with AUTOEXTEND, measuring space usage based on datafiles does not provide an accurate picture. Instead, what you want is a tablespace level picture.

Eg. CREATE TABLESPACE CPS_NDX1_TSP DATAFILE SIZE 25G AUTOEXTEND ON MAXSIZE 90G;

Using the query above, here is the output generated :

Tablespace Used MB Free MB Total MB Percent Free

CPS_TSP 53950 2850 56800 5

This output does not take into account that the datafile can extend upto 90G.

Instead, I found this query here more useful :

column USED_PERCENT format 999.99

column FREE_PERCENT format 999.99

select TABLESPACE_NAME,USED_PERCENT,100-USED_PERCENT "FREE_PERCENT" from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME USED_PERCENT FREE_PERCENT

CPS_TSP 56.54 43.46

Here, I see that %free for this tablespace is 43.46