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.
May 25th, 2018, 1:58am
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 3878 times)
Pete Finnigan
PeteFinnigan.com Administrator
*****




Oracle Security is easier if you design for it

   
View Profile | WWW | Email

Gender: male
Posts: 309
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

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
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