Re: sqlplus checking tablespace

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Tue, 22 Aug 2000 14:13:05 GMT
Message-ID: <8nu1ov$f1b$1@nnrp1.deja.com>

In article <8nrlk5$n9v$1_at_nnrp1.deja.com>,   tony_barratt_at_my-deja.com wrote:
> 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
>

Your first problem is that sys.dba_free_space does not have entries for tablespaces with no free space so this report will ignore 100% full tablespaces:

Try this:
 select '&database '||ts.tablespace_name

       , decode(nvl(fs.FSPACE,0),'0','NOT OK','OK') Status from sys.dba_Tablespaces ts

       , ( select b.tablespace_name, (max(b.bytes/1024)) as FSPACE
            from  sys.dba_free_space b
         group by b.tablespace_name
         having max(b.bytes) < &kbytes*1024
         ) fs

where ts.tablespace_name = fs.tablespace_name(+)

which produced this -
'UT'||TS.TABLESPACE_NAME STATUS

--------------------------------- ------
UT DATASPC                        NOT OK
UT IDXSPC                         OK
UT PRODUCT                        NOT OK
UT RBS                            NOT OK
UT SYSTEM                         NOT OK
UT TMP                            NOT OK
UT TMPDQ3                         NOT OK
UT USR                            OK

8 rows selected.

You may wish to exclude your sort segment tablespac [tmp in my case] and your rollback segment tablespaces [rbs in example] since you are probably really interested in table/index tablespaces in this report.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Aug 22 2000 - 09:13:05 CDT