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
