Tablespace free space
                                                 Last update (2011-04-06 10:35:26)
                                                                                                                   Date added (2008-02-09 20:30:23)

Summary
Some usefull information about tablespaces (Temporay tablespace not included for Oracle9i). The most important column is PCT_FREE (percentage free). For example 1% means that we run out of space soon!
SELECT * FROM (SELECT c.*, d.CONTENTS, d.extent_management, d.allocation_type,d.MAX_EXTENTS,
d.initial_extent/1024 || ' KB' INIT_EXT, d.next_extent/1024 || ' KB' NEXT_EXT FROM
(SELECT NVL(b.tablespace_name, NVL(a.tablespace_name,'UNKOWN')) tablespace_name,
ROUND(Mbytes_alloc) MB_Alloc,
ROUND(NVL(Mbytes_free,0)) MB_Free,
ROUND(Mbytes_alloc-NVL(Mbytes_free,0)) MB_Used,
ROUND(((NVL(Mbytes_free,0))/Mbytes_alloc)*100) pct_free,
ROUND(((Mbytes_alloc-NVL(Mbytes_free,0))/Mbytes_alloc)*100) pct_used,
DECODE(Mbytes_max, 0, 'NO', 'AUTO') EXTENSIBLE
FROM (SELECT SUM(bytes)/(1024*1024) Mbytes_free,
MAX(bytes)/(1024*1024) largest,
tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name ) a,
(SELECT SUM(bytes)/(1024*1024) Mbytes_alloc,
SUM(maxbytes)/(1024*1024) Mbytes_max,
tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name (+) = b.tablespace_name
--AND a.tablespace_name LIKE 'RA_CUSTOMER%'
) c, dba_tablespaces d
WHERE c.tablespace_name = d.tablespace_name)
ORDER BY pct_free ASC;
Database size in GBytes
SELECT SUM(MB_ALLOC)/1024 GB_DB_SIZE FROM 
(
SELECT SUM(ROUND(bytes_used/(1024*1024),2) + ROUND(bytes_free/(1024*1024),2)) MB_ALLOC 
FROM V$temp_space_header, dba_temp_files 
WHERE V$temp_space_header.file_id (+) = dba_temp_files.file_id
UNION
SELECT SUM(BYTES)/(1024*1024) MB_ALLOC FROM dba_data_files
); 
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