Use below query to check all the space related details of tablespaces.
set feedback off set pagesize 70; set linesize 2000 set head on COLUMN Tablespace format a25 heading 'Tablespace Name' COLUMN autoextensible format a11 heading 'AutoExtend' COLUMN files_in_tablespace format 999 heading 'Files' COLUMN total_tablespace_space format 99999999 heading 'TotalSpace' COLUMN total_used_space format 99999999 heading 'UsedSpace' COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace' COLUMN total_used_pct format 9999 heading '%Used' COLUMN total_free_pct format 9999 heading '%Free' COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto' COLUM total_auto_used_pct format 999.99 heading 'Max%Used' COLUMN total_auto_free_pct format 999.99 heading 'Max%Free' WITH tbs_auto AS (SELECT DISTINCT tablespace_name, autoextensible FROM dba_data_files WHERE autoextensible = 'YES'), files AS (SELECT tablespace_name, COUNT (*) tbs_files, SUM (BYTES/1024/1024) total_tbs_bytes FROM dba_data_files GROUP BY tablespace_name), fragments AS (SELECT tablespace_name, COUNT (*) tbs_fragments, SUM (BYTES)/1024/1024 total_tbs_free_bytes, MAX (BYTES)/1024/1024 max_free_chunk_bytes FROM dba_free_space GROUP BY tablespace_name), AUTOEXTEND AS (SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow FROM dba_data_files WHERE autoextensible = 'YES' GROUP BY tablespace_name UNION SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow FROM dba_data_files WHERE autoextensible = 'NO' GROUP BY tablespace_name) GROUP BY tablespace_name) SELECT c.instance_name,a.tablespace_name Tablespace, CASE tbs_auto.autoextensible WHEN 'YES' THEN 'YES' ELSE 'NO' END AS autoextensible, files.tbs_files files_in_tablespace, files.total_tbs_bytes total_tablespace_space, (files.total_tbs_bytes - fragments.total_tbs_free_bytes ) total_used_space, fragments.total_tbs_free_bytes total_tablespace_free_space, round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes) / files.total_tbs_bytes ) * 100 )) total_used_pct, round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100 )) total_free_pct FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto WHERE a.tablespace_name = files.tablespace_name AND a.tablespace_name = fragments.tablespace_name AND a.tablespace_name = AUTOEXTEND.tablespace_name AND a.tablespace_name = tbs_auto.tablespace_name(+) order by total_free_pct;