I am not if I can post this query in this section, but as I am executing this statement on repository DB , I assumed to post here.
I have a requirement to calculate the database sizes from the tables in repository.
table 1 : MGMT$DB_TABLESPACES_ALL : has the information on all the tablespace information from each database. and it has information of all the databases.
table 2: MGMT$DB_REDOLOGS_ALL : has the information on all the redolog files information from each database. and it has information of all the databases.
Now to calculate the size of each database , we need to collect the sizes from both tables and then need to add.
below is the sql i prepared and sql didn't return and systax error , but the final output is completely wrong. Seems like its multiplying the data somewhere and getting the wrong information.
please help me in doing the required modifications.
select a.TARGET_NAME "DATABASE_NAME" ,sum(a.TABLESPACE_SIZE/1024/1024/1024)+sum(b.LOGSIZE/1024/1024/1024/1024) "DATABASE ALLOCATED SPACE(GB)",
sum(a.TABLESPACE_USED_SIZE/1024/1024/1024)+sum(b.LOGSIZE/1024/1024/1024/1024) "DATABASE USED SPACE(GB)",
sum(a.TABLESPACE_SIZE/1024/1024/1024)-sum(a.TABLESPACE_USED_SIZE/1024/1024/1024) "DATABASE FREE SPACE(GB)"
from MGMT$DB_TABLESPACES_ALL a ,MGMT$DB_REDOLOGS_ALL b
where a.TARGET_NAME=b.TARGET_NAME and a.TARGET_Name like '%d2oem%'
group by a.target_name;
Note: There are multiple records having the same TARGET_NAME value in both the tables.