-- User Tablespace available details

SELECT CAST(DECODE(GROUPING(tablespace_name), 
  0, TO_CHAR(tablespace_name),
  'Total  ('|| TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||')')
   AS VARCHAR2(30)) 
  table_space_Details,
  SUM(bytes)/1024/1024 ts_available_mb
FROM  user_free_space
GROUP BY ROLLUP(tablespace_name);

=> User_Free_Space Details

-- Tablespace and file size details

SELECT tablespace_name, file_id,
 SUM(bytes)/1024/1024 size_mb
from sys.dba_free_space
GROUP BY tablespace_name, file_id
ORDER BY file_id;




-- Tablespace - used, reserved and free space details

SELECT
 SUM(q_tab.used_size)  used_space,
 SUM(q_tab.reserved_size)  reserved_space,
 SUM(q_tab.reserved_size)-SUM(q_tab.used_size) free_space 
FROM
(SELECT  0 used_size, SUM(bytes)/1014/1024 reserved_size
 FROM sys.dba_data_files
 UNION
 SELECT  SUM(bytes)/1014/1024 used_size, 0 reserved_size
 FROM sys.dba_free_space
) q_tab;




SELECT TO_CHAR(sysdate, 'mm/dd/yyyy') as_of, 
  SUM(bytes)/1024/1024/1024 used_size_gb
FROM sys.dba_extents;



-- Tablespace, segment, type, bytes and blocks details

SELECT  
 e.extent_id,
 s.Segment_Name, s.Segment_Type, s.tablespace_name,
 s.Bytes,
 s.blocks,
 s.Bytes/q_conv.value eval_Blocks
FROM dba_Segments S,  dba_Extents E,
 (select value
  from   v$parameter
  where name = 'db_block_size') q_conv
WHERE e.Segment_Name = s.Segment_Name (+)
ORDER BY s.Segment_Name, e.extent_id;

-- Tablespace quota details

SELECT * FROM dba_ts_quotas;