Oracle Enterprise Manager query table space sizes

Oracle Enterprise Manager provides you the ideal solution to manage a large number of targets. All information about the targets, for example Oracle databases, is stored in the Oracle Enterprise Manager Repository database. What makes it interesting is that you can query the database with SQL and get information out of it quickly, showing you exactly what you need.

In the below example we do query the total size of the Oracle database tablesize per database. The query provides a list of all databases that are registered as a target in OEM in combination with the name of the server it is running on and the total size of the table space.

SELECT
      HOST_NAME,
      TARGET_NAME,
      round(SUM(TABLESPACE_SIZE)/1024/1024,0) AS "DB_TABLESPACE_USED"
FROM 
    MGMT$DB_TABLESPACES
GROUP BY
        target_name,
        host_name
ORDER BY 
        host_name, 
        target_name

The code is also available on github where you can find a larger collection of scripts. This scripting repository will be updated continuously so everyone is able to make use of the scripts.