Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Welcome, Guest. Please Login.
Nov 23rd, 2017, 7:07am
News: Welcome to Pete Finnigan's Oracle security forum
Home | Help | Search | Members | Login
   Pete Finnigan's Oracle Security Forum
   Programming and Scripting
   Programming and Scripting
(Moderator: Pete Finnigan)
   Automize Shell Script get tablespace details
« No topic | Next topic »
Pages: 1  Reply | Notify of replies | Send Topic | Print
   Author  Topic: Automize Shell Script get tablespace details  (Read 3703 times)
helena
PeteFinnigan.com Newbie
*



I love YaBB 1G - SP1!

   
View Profile | Email

Posts: 6
Automize Shell Script get tablespace details
« on: Aug 2nd, 2007, 1:19pm »
Quote | Modify

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
 Wink
IP Logged
Pages: 1  Reply | Notify of replies | Send Topic | Print

« No topic | Next topic »

Powered by YaBB 1 Gold - SP 1.4!
Forum software copyright 2000-2004 Yet another Bulletin Board