Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Programming and Scripting >> Programming and Scripting >> Automize Shell Script get tablespace details
        
(Message started by: Pete Finnigan on Aug 2^nd, 2007, 1:19pm)

Title: Automize Shell Script get tablespace details
Post by Pete Finnigan on Aug 2^nd, 2007, 1:19pm

Hi

I am fresh enough to Shell Scripting and learning scripting.

I need to write a Shell script which will run on Unix box to get tablespace details wrt to TABLESPACE_NAME,TOTAL_SPACE(MB),FREE_SPACE(MB),PCT_FREE,PCT_USED
.

Currently I having a script which captures all these details ,could any one put it in shell script format and help me out.

SELECT a.tablespace_name, ROUND (a.bytes_alloc / 1024 / 1024, 2) TOTAL_SPACE_MB,
ROUND (NVL (b.bytes_free, 0) / 1024 / 1024, 2) FREE_SPACE_MB,
ROUND ((a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024,2) megs_used,
ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_free,
100 - ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_used,
ROUND (maxbytes / 1048576, 2) MAX
FROM (SELECT f.tablespace_name, SUM (f.BYTES) bytes_alloc,
SUM (DECODE (f.autoextensible,
'YES', f.maxbytes,
'NO', f.BYTES
)
) maxbytes
FROM dba_data_files f
GROUP BY tablespace_name) a,
(SELECT f.tablespace_name, SUM (f.BYTES) bytes_free
FROM dba_free_space f
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
SELECT h.tablespace_name,
ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576, 2) TOTAL_SPACE_MB,
ROUND ( SUM ((h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))/ 1048576,2
) FREE_SPACE_MB,
ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576, 2) megs_used,
ROUND ( ( SUM ( (h.bytes_free + h.bytes_used)
- NVL (p.bytes_used, 0)
)
/ SUM (h.bytes_used + h.bytes_free)
)
* 100,2
) pct_free,
100
- ROUND ( ( SUM ( (h.bytes_free + h.bytes_used)
- NVL (p.bytes_used, 0)
)
/ SUM (h.bytes_used + h.bytes_free)
)
* 100,
2
) pct_used,
ROUND (f.maxbytes / 1048576, 2) MAX
FROM SYS.v_$temp_space_header h,
SYS.v_$temp_extent_pool p,
dba_temp_files f
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
AND f.file_id = h.file_id
AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name, f.maxbytes
ORDER BY 1
;)