Oracle query results don't match Enterprise Manager values

Question

I am trying to get the size of all tablespaces on an Oracle database. I have tried 2 ways of getting the info.

Option 1 gives me good values, but it doesn't give return any values when the tablespace doesn't have a database file. Specifically, it isn't returning any values for the "TEMP" and "TESTTODELETE" tablespaces.

The only differences I've noticed between these 2 tablespaces and the others are that these 2 don't have .dbf files.

Option 2 gives me the correct values for some tablespaces, but is totally off other times. Option 2 does return something for the "TEMP" tablespace, but it doesn't return anything for the "TESTTODELETE" tablespace.

Both options don't return any information for the tablespace "TESTTODELETE".

What is the best way to get the tablespace total size in MB for all tablespaces so that it reflects what is displayed in the Enterprise Manager?

Answer 1

dba_data_files contains just that, tablespaces that have data files. If you need to include tablespaces that aren't backed by data files (e.g. temp tablespaces), you'll need to include dba_temp_files in your query. They have almost exactly the same column layout so it should be easy to UNION them together.

As for dba_tablespace_usage_metrics, the capacity it reports is the maximum size for a tablespace. If a tablespace has autoextend enabled it will include that in the calculation. Furthermore, I don't recommend relying on the v$parameters to determine block size. Instead, join to dba_tablespaces because each tablespace can have its own block size.