SELECT
 SUBSTR(file_name,1,45) files_name, tablespace_name, blocks,
 bytes, status
FROM sys.dba_data_files;


SELECT  
 file_id,
 SUBSTR(file_name,1,30) files_name, tablespace_name, blocks,
 ROUND(bytes/1024/1024,2) space_mb,
 ROUND(bytes/1024/1024/1024,3) space_gb, status
FROM   sys.dba_data_files
WHERE  INSTR(tablespace_name, UPPER('&i_tablespace')) > 0
ORDER BY file_id;
-- Above query output for System tablespace


-- v$datafile synonym of sys.v_$datafile
SELECT  ROUND(SUM(bytes)/1024/1024/1024,3)  filesize_gb
FROM v$datafile;

SELECT  
 file# file_no, name file_name,  
 ROUND(bytes/1024/1024/1024,3) size_gb, blocks, status,  
 TO_CHAR(online_time,'mm/dd/yyyy hh24:mi:ss') online_time,  
 TO_CHAR(last_time,'mm/dd/yyyy hh24:mi:ss') last_time,  
 TO_CHAR(creation_time,'mm/dd/yyyy hh24:mi:ss') creation_time  
FROM v$datafile;


-- v$filestat synonym of sys.v_$filestat
SELECT
 DECODE(GROUPING(SUBSTR(df.file_name,1,40)),
  0,SUBSTR(df.file_name,1,40),
  'Total ('||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||')')
 details,
 SUM(fs.phyrds) reads,
 SUM(fs.phywrts) writes
FROM  v$filestat fs,
      sys.dba_data_files df
WHERE fs.file# = df.file_id
GROUP BY ROLLUP(SUBSTR(df.file_name,1,40));