Posted By

thescorpion on 10/05/12


Tagged

sql PLSQL DBA tablespace


Versions (?)

Consultar el MB los tamaños y porcentaje libre de TableSpace Oracle


 / Published in: SQL
 

Consular los tablespace y mostrar su tamaño , uso y espacio libre en Megabyte

  1. SELECT /* + RULE */ df.tablespace_name "Tablespace",
  2. df.bytes / (1024 * 1024) "Size (MB)",
  3. SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
  4. Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
  5. Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  6. FROM dba_free_space fs,
  7. (SELECT tablespace_name,SUM(bytes) bytes
  8. FROM dba_data_files
  9. GROUP BY tablespace_name) df
  10. WHERE fs.tablespace_name (+) = df.tablespace_name
  11. GROUP BY df.tablespace_name,df.bytes
  12. UNION ALL
  13. SELECT /* + RULE */ df.tablespace_name tspace,
  14. fs.bytes / (1024 * 1024),
  15. SUM(df.bytes_free) / (1024 * 1024),
  16. Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
  17. Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  18. FROM dba_temp_files fs,
  19. (SELECT tablespace_name,bytes_free,bytes_used
  20. FROM v$temp_space_header
  21. GROUP BY tablespace_name,bytes_free,bytes_used) df
  22. WHERE fs.tablespace_name (+) = df.tablespace_name
  23. GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
  24. ORDER BY 4 DESC;

Report this snippet  

You need to login to post a comment.