SQL doesn't always work

[QAGuy]

Thanks for your help.

I would like to use this SQL statement in a VB program I'm writing:
select sum(dba_free_space.bytes), sum(dba_data_files.bytes), sum(dba_extents.bytes)
from dba_free_space, dba_data_files, dba_extents
where dba_free_space.tablespace_name=' ' AND
dba_free_space.tablespace_name=' ' AND
dba_extents.tablespace_name=' ';

This should return the total size, used and free space of a tablespace. It works fine except when there is a new tablespace that has not been written to yet. It will not be in the dba_extents table, which will cause the whole statement to fail. Anyone know a way around this?

Thanks

 

[carp]

QAGuy -
How about this:

1 select a.tablespace_name, a.num_bytes - b.num_bytes freespace
2 from (select tablespace_name, sum(bytes) num_bytes from dba_data_files group by tablespace_name) a,
3 (select tablespace_name, sum(bytes) num_bytes from dba_extents group by tablespace_name) b
4* where a.tablespace_name = b.tablespace_name
SQL> /

TABLESPACE_NAME FREESPACE
------------------------------ ----------
INDX 168132608
LOOKUP_TS 50626560
RBS 315383808
SYSTEM 524288
TEMP 178831360
TOOLS 47349760
USERS 746037248

 

[carp]

Ooops. That still won't work!
Try this instead:

1 select a.tablespace_name, a.num_bytes - b.num_bytes freespace
2 from (select tablespace_name, sum(bytes) num_bytes from dba_data_files group by tablespace_name) a,
3 (select tablespace_name, sum(bytes) num_bytes from dba_extents group by tablespace_name) b
4* where a.tablespace_name(+) = b.tablespace_name
SQL> /

TABLESPACE_NAME FREESPACE
------------------------------ ----------
INDX 168132608
LOOKUP_TS 50626560
RBS 315383808
SYSTEM 524288
TEMP 178831360
TOOLS 47349760
USERS 746037248

7 rows selected.

 

[QAGuy]

That works, but I was hoping to get them all in one statement if possible. Thanks for the suggestion, I appreciate it.

 

[carp]

Perhaps this is more to your liking:

1 select a.tablespace_name, sum(a.bytes) - sum(b.bytes)
2 from dba_data_files a,
3 dba_extents b
4 where a.tablespace_name(+) = b.tablespace_name
5* group by a.tablespace_name
SQL> /

TABLESPACE_NAME SUM(A.BYTES)-SUM(B.BYTES)
------------------------------ -------------------------
INDX 9806888960
LOOKUP_TS 50626560
RBS 2.0795E+10
SYSTEM 1.1073E+11
TEMP 6050856960
TOOLS 1.2054E+10
USERS 2.4792E+10

 

[karluk]

I don't think that QAGuy's original query and Carp's latest suggestion work right, even if there isn't an issue with new tablespaces. Doing this sort of join produces a sum over more rows than are in dba_data_files. This produces an unrealistically high value for free space, as you can see by comparing the result sets from Carp's three queries. Better stick with Carp's previous suggestion.

 

[carp]

Concur! I guess that's the problem with answering a forum, eating lunch, and working at the same time. At least I got my sandwich right!

 

[karluk]

The last time I tried to do that many things at once I accidentally took a bite out of my computer instead of my sandwich.

 

[carp]

Ah! So I COULD have done worse!!

 

[CRoberts]

I am not quite sure if you want a total of all space useage, or a total of all usage by tablespace name. If you want it by tablespace, you could use the following (The original query is missing relating the three files together properly).

SELECT
dba_data_files.Tablespace_Name,
NVL(SUM(dba_free_space.bytes), 0) AS FreeSpace,
NVL(SUM(dba_data_files.bytes) ,0) AS DataFiles,
NVL(SUM(dba_extents.bytes), 0) AS Extents
FROM
Dba_data_files,
Dba_extents,
Dba_free_space
WHERE
Dba_Data_Files.Tablespace_Name = Dba_extents.Tablespace_name(+)
AND Dba_Data_Files.Tablespace_Name = Dba_free_space.Tablespace_name(+)
GROUP BY dba_data_files.Tablespace_Name

 

[karluk]

That's a good try, CRoberts, but your query has the same problem as QAGuy's and Carp's third suggestion - it does a sum over all the rows of a join, rather than over all the rows in the underlying catalog views. As a result the sums are generally not correct - they are almost always much too large.

I tried your query on my own database and got numbers like 800 GB allocated to a single tablespace. For some servers that may be realistic, but for me it is far more disk space than I have on my entire server.