- Newest
- Most votes
- Most comments
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
Relevant content
- asked 6 months ago
- AWS OFFICIALUpdated 5 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 5 months ago
- AWS OFFICIALUpdated a year ago
Thank you. Do you mean to trigger the DB stored procedure via Lambda function and trigger the Lambda using EventBridge? How do I create an alarm based on a specific metric?