How to list tablespaces and their datafiles in Oracle

Every tablespace with its datafiles, size, and autoextend. Temp tablespaces keep their files separately; use the temp usage script for those.

SELECT t.tablespace_name,
       t.contents,
       t.status,
       t.extent_management,
       d.file_name,
       ROUND(d.bytes/1024/1024) AS size_mb,
       d.autoextensible
FROM   dba_tablespaces t
       LEFT JOIN dba_data_files d ON d.tablespace_name = t.tablespace_name
ORDER  BY t.tablespace_name, d.file_name;