Home » RDBMS Server » Server Administration » Script to Monitor Table Space
Script to Monitor Table Space [message #216020] Wed, 24 January 2007 12:37 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,
I have a Unix script to monitor tablespaces and the output of it should be in a txt file, which i will monitor daily, but the contents of txt file is some thing different,
here is the script and output
mcc1asff4:/export/home/archieu/preprocessor>more stats.sh
#!/usr/bin/ksh
echo hello
echo $ORACLE_HOME
sample=`/orcl/app/oracle/product/8.1.7/bin/sqlplus  archuser/archuser@database << END >/export/home/arch/preprocessor/
ravi.txt
set head off
set echo  off
set verify off
set termout off
set feedback off
set pagesize 0
set show off

SELECT 'Tablespace:'||df.tablespace_name||', MB Free: '||ROUND(SUM(fs.MB_free)/(1024*1024))
        || ', MB Total: '||SUM(df.bytes)/(1024*1024)|| ', PCT Free: '||ROUND(100*SUM(fs.MB_free)/SUM(df.bytes))||'%'
FROM
 sys.dba_data_files df,
 (SELECT
  fs.tablespace_name,
  fs.file_id,
  SUM(fs.bytes) MB_free
  FROM
  sys.dba_free_space fs
  GROUP BY
  fs.tablespace_name, fs.file_id
 ) fs
WHERE
 fs.tablespace_name = df.tablespace_name AND
 fs.file_id = df.file_id
GROUP BY df.tabLespace_name


exit

END`

when i run the script and he Output in the Text file is
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jan 24 13:29:31 2007

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
18  SQL> SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production


What is wrong in the Shell script.


Thanks

[Updated on: Wed, 24 January 2007 12:38]

Report message to a moderator

Re: Script to Monitor Table Space [message #216021 is a reply to message #216020] Wed, 24 January 2007 12:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>GROUP BY df.tabLespace_name
Missing the terminator.
It should be
GROUP BY df.tabLespace_name;

Re: Script to Monitor Table Space [message #216023 is a reply to message #216021] Wed, 24 January 2007 12:53 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And to make it a little neat,
you may want to use
sqlplus -s archuser/archuser@database

Just search this forum.
THere are many better formats available.

And
>>which i will monitor daily
Pain. Why not just automate that too?
You can email yourself if any tablespace is more than say, 90% used ? You can make it any fancy you want.

[Updated on: Wed, 24 January 2007 13:03]

Report message to a moderator

Previous Topic: Archive in multiple destination
Next Topic: USA 2007 DST Changes: Frequently Asked Questions for Oracle RDBMS
Goto Forum:
  


Current Time: Fri Dec 02 20:29:00 CST 2016

Total time taken to generate the page: 0.15144 seconds