From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level, see blog post:
How to obtain the Database Total Used, Allocated and Max Size
However, it’s also good to know the historical size. The below query will give you this from the Oracle Enterprise Manager (OEM) repository:
SELECT Database, Month_Date, round(sum(decode(metric_column, 'spaceUsed', maximum))/1024/1024, 3) Used_Size_Tb, round(sum(decode(metric_column, 'spaceAllocated', maximum))/1024/1024, 3) Allocated_Size_Tb FROM ( SELECT target_name Database, trunc(rollup_timestamp, 'MONTH') Month_Date, key_value TB, metric_column, round(max(maximum),0) maximum FROM mgmt$metric_daily WHERE target_type = 'rac_database' and metric_name = 'tbspAllocation' and metric_column in ('spaceAllocated', 'spaceUsed') and target_name in ('VERS') GROUP BY target_name, key_value, trunc(rollup_timestamp, 'MONTH'), metric_column ) GROUP BY Database, Month_Date ORDER BY Database, Month_Date /
DATABASE MONTH_DAT USED_SIZE_TB ALLOCATED_SIZE_TB ---------- --------- ------------ ----------------- VERS 01-SEP-15 1.198 1.554 VERS 01-OCT-15 1.209 1.652 VERS 01-NOV-15 1.3 1.805 ... VERS 01-MAY-17 6.526 7.226 VERS 01-JUN-17 7.085 8.528 VERS 01-JUL-17 7.136 7.569 23 rows selected. SQL>
The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Zed DBA (Zahid Anwar)
3 thoughts on “How to obtain the Historical Database Total Used and Allocated Size from OEM Repository”
Pingback: How to obtain the Database Total Used, Allocated and Max Size | Zed DBA's Oracle Blog
Nice blog thanks for postingg
Thank you and thanks for visiting 🙂