Datafiles free space
                                                 Last update (2008-02-19 17:23:18)
                                                                                                                   Date added (2008-02-12 14:48:15)

In which filesystems the datafiles are stored
SELECT DISTINCT(SUBSTR(file_name, 0, 20)) mount_point FROM dba_data_files
--where tablespace_name = 'REPDATA';
How much free space is available for possible datafiles resizing
SELECT * FROM (
SELECT SUBSTR(file_name, 0, 17) MOUNT_POINT, SUM(dba_free_space.bytes)/(1024*1024*1024) GB_FREE_SPACE
FROM dba_data_files, dba_free_space
WHERE dba_free_space.file_id = dba_data_files.file_id
GROUP BY SUBSTR(file_name, 0, 17))
ORDER BY GB_FREE_SPACE DESC;
For autoextend datafiles how more you can extend in Mbytes
SELECT df.file_id, df.file_name, df.tablespace_name, (df.bytes/(1024*1024)) "Size MB", ROUND((df.maxbytes/(1024*1024)),2) "Max MB",
ROUND((df.bytes-NVL(fs.free_space, 0))/(1024*1024),2) "Used MB",
ROUND((df.maxbytes/(1024*1024)),2) - ROUND((df.bytes-NVL(fs.free_space, 0))/(1024*1024),2) "More to extent MB"
FROM (SELECT file_id, SUM(bytes) free_space FROM dba_free_space
GROUP BY file_id) FS,
(SELECT file_id,file_name, blocks,bytes, maxbytes, maxblocks, tablespace_name, user_bytes
FROM dba_data_files
WHERE AUTOEXTENSIBLE ='YES'
--and tablespace_name = 'GRAPHS_TEMP'
) DF
WHERE fs.file_id(+) = df.file_id
ORDER BY file_id ASC;
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search