Know size of all databases from OMR – #EM

I have been asked to know all the databases size and export them in excel file, that’s all!!!  We are using EM 11.1 of course old but that is no matter for me and in total we have 173 target databases and to check from each database manually then i have to login each server, setting the environment and so on will be taking many hours, we know that using EM we can extract something using the reports. When checked from the reports we can export in to graphs, html so on but there is no option seen with the excel file, then finally i thought to use the OMR database as my weapon to extract.  In this process reviewed MOS and found various queries which starts with MGMT_ so on but it is not giving proper information what i need. Hence finally i found one view “mgmt$db_tablespaces” which can fetch the tablespace size and also usage. is that fruitful query right? Now let’s see that that query is..

select host_name,target_name,sum(tablespace_used_size/1024/1024/1024),sum( TABLESPACE_SIZE/1024/1024/1024) from mgmt$db_tablespaces group by host_name,target_nam

wow, is that so simple? YES. Check the output below. 

SQL> select host_name,target_name,sum(tablespace_used_size/1024/1024/1024),sum( TABLESPACE_SIZE/1024/1024/1024) from mgmt$db_tablespaces group by host_name,target_name;

HOST_NAME                  TARGET_NAME SUM(TABLESPACE_USED_SIZE/1024/1024/1024)          SUM(TABLESPACE_SIZE/1024/1024/1024)
-------------------------- -------------------- ---------------------------------------- -----------------------------------
ora-c1.localdomain         sjmaprc              316.732605                                 382.602051
ora-c2.localdomain         DD124X02            1285.68835                                1613.77147
ora-u1.localdomain         uat92001             632.908569                                759.611084
ora-u2.localdomain         dq114x04             168.662659                                537.98584

Note down that the size of the bytes in GB. Probably many of them know about this view but many queries and various tables/views used in order to fetch the same output, i really liked the simple query which can gives sizes of all databases. Thanks for reading. 🙂

References:  https://docs.oracle.com/cd/E24628_01/doc.121/e57277/ch7_configmanviews.htm#EMVWS32102