Scriptorium

us fr nl




SQL : Uncategorized: Check Free space in Tablespaces for Oracle 8.0   us


Options: Save as PDF | Save attached file | Toggle line numbers

Details:

Type: query
Added By: Rembo
Short Description:
This query checks the free space in the Oracle tablespaces. 
Notes:
Not tested on newer Oracle version.
Added: Jun 6 2005 at 4:01 PM
Modified: Nov 8 2005 at 8:47 AM
Related URLs


Usage:

Run the query against an Oracle 7.x or 8.x database. Most easy is to run the query
in the SQL+ editor by copy and pasting or safe the code to a file query.sql and
start it from the SQL+ editor prompt (start <yourpath>\query.sql)


Code:

Formatted | Unformatted
  1. SELECT
  2. a.file_id,
  3. substr(a.tablespace_name,1,14) tablespace_name,
  4. trunc(decode(a.autoextensible,'YES',a.maxsize-a.bytes+b.free,'NO',b.free)/1024/1024) free_mb,
  5. trunc(a.bytes/1024/1024) size_mb,
  6. trunc(a.maxsize/1024/1024) maxsize_mb,
  7. a.autoextensible ae,
  8. trunc(decode(a.autoextensible,'YES',(a.maxsize-a.bytes+b.free)/a.maxsize*100,'NO',b.free/a.maxsize*100)) free_pct
  9. FROM
  10. (SELECT file_id,
  11. tablespace_name,
  12. autoextensible,
  13. bytes,
  14. decode(autoextensible,'YES',maxbytes,bytes) maxsize
  15. FROM dba_data_files
  16. GROUP BY file_id,
  17. tablespace_name,
  18. autoextensible,
  19. bytes,
  20. decode(autoextensible,'YES',maxbytes,bytes)) a,
  21. (SELECT file_id,
  22. tablespace_name,
  23. sum(bytes) free
  24. FROM dba_free_space
  25. GROUP BY file_id,
  26. tablespace_name) b
  27. WHERE a.file_id=b.file_id(+)
  28. AND a.tablespace_name=b.tablespace_name(+)
  29. ORDER BY a.tablespace_name ASC;



User comments :

Add a new comment   Back to Top
Atom Feed
Contact | About This Application | Scriptorium Website