Oracle Database

--
-- View Datafile Info.
--
 
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
SET PAGESIZE 60
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
COL mbytes HEA " MBytes " FOR 99999.999
COL tablespace_name HEA "    Tablespace    " FOR a18
COL file_name HEA "                   DBF Name                  " FOR a45
COL ts# HEA "TS#" FOR 99
 
SELECT
   ts.tablespace_name, vts.ts#, ts.file_name, ts.mbytes
FROM
   v$tablespace vts,
   (
   SELECT 
      tablespace_name, file_id, file_name, ( bytes / 1048576 ) mbytes
   FROM
      sys.dba_data_files
   UNION
   SELECT 
      tablespace_name, file_id, file_name, ( bytes / 1048576 ) mbytes
   FROM
      sys.dba_temp_files
   ) ts
WHERE
   vts.name = ts.tablespace_name
ORDER BY
   ts.tablespace_name, ts.file_id
/