sqlplus checking tablespace

From: <tony_barratt_at_my-deja.com>
Date: Mon, 21 Aug 2000 16:33:10 GMT
Message-ID: <8nrlk5$n9v$1@nnrp1.deja.com>

Hi List,
Maybe a sqlplus person can help me out a bit: I have some sqlplus code that checks free table space and it works and that's good. Howvever if the tablespaces are OK it's silent (working I suppose, as designed).
How ever, I want to invoke this code remotely on the server, and I'd like a line that says:
check_tablespaces OK
OR
check_tablespaces NOTOK
report line 1
[...]
report last line

Although i speak C, perl, ksh and a few other lingos, I don't know the sysntax of SQLplus, and I just want to do this one small change, and
<sniff> I haven't got time read yet another 700 page fine manual.
So If you know how to do it please tell me....

Regards

Tony

check_tablespaces.sql

set feedback off
<snip options>

set trimspool on
define kbytes=5000
column c1 noprint new_value database
select name c1
  from v$database ;
column c2 format a100 trunc
set termout on

select '&database'||': '||tablespace_name||
       ' Freespace '|| round(max(bytes)/1024,0)||
       'K (Check < '||&kbytes||'K)'  c2

  from sys.dba_free_space
 group by tablespace_name
having max(bytes) < &kbytes*1024;
exit

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Aug 21 2000 - 11:33:10 CDT