Advertisement
Guest User

Untitled

a guest
Jan 16th, 2017
81
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.31 KB | None | 0 0
  1. sqlplus / as sysdba << EOF > /home/oracle/scripts/XXXSTablespace.TXT
  2. @/home/oracle/scripts/XXXTablespace.sql
  3. exit;
  4. !
  5.  
  6. set feed off
  7. set linesize 100
  8. set pagesize 200
  9.  
  10. spool XXXTablespace.txt
  11.  
  12. SELECT F.TABLESPACE_NAME,
  13. TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
  14. TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
  15. TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
  16. TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
  17. FROM (
  18. SELECT TABLESPACE_NAME,
  19. ROUND (SUM (BLOCKS*(SELECT VALUE/1024
  20. FROM V$PARAMETER
  21. WHERE NAME = 'db_block_size')/1024)
  22. ) FREE_SPACE
  23. FROM DBA_FREE_SPACE
  24. GROUP BY TABLESPACE_NAME
  25. ) F,
  26. (
  27. SELECT TABLESPACE_NAME,
  28. ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
  29. FROM DBA_DATA_FILES
  30. GROUP BY TABLESPACE_NAME
  31. ) T
  32. WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
  33. AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
  34. spool off
  35. exit
  36. !
  37.  
  38.  
  39. if [ 'cat XXXTablespace.txt|wc -l' -gt 0 ]
  40. then
  41. cat XXXTablespace.txt -l XXXTablespace.txt > /home/oracle/scripts/XXXTablespace.TXT
  42. mail -s "XXX Tablespace Usage" XXX@YYYY.com < /home/oracle/scripts/XXXTablespace.TXT
  43.  
  44. fi
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement