Free Oracle UX Command Line Tool: orastat

From: Bill & Mary Border <bb22_at_adelphia.net>
Date: Sat, 07 Dec 2002 23:44:49 GMT
Message-ID: <RJvI9.464$VA5.274360@news1.news.adelphia.net>

Hello All,

FYI ... I've had a WWW site for the past few years where I have made available a free download-able tool which we find greatly simplifies DBA tasks. It can be downloaded from:

http://dbamon.com

For example, if you want to find out how full your tablespaces are, without orastat you would have to type a query that looks like:

select    a.tablespace_name as tablespace_name,
          d.contents,
          a.sum_bytes as bytes_avail,
          a.max_id,
          nvl(b.sum_bytes,0) as bytes_used,
          nvl(c.sum_bytes,0) as bytes_free,
          round(nvl(b.sum_bytes,0.00) / a.sum_bytes * 100.0, 2) as pct_used,
          d.status     from           ( select
tablespace_name,
              sum(bytes) sum_bytes,              count(1) max_id
            from               dba_data_files            group by
              tablespace_name          ) a,          ( select

          (yada, yada ...)

With orastat, you would simply run orastat -ts and see the following output:

Tablespace Type Status NumDF Size(MB) Free(MB) Used(MB) Pct
==================== ==== ======== ===== ========= ========= ========= =====

RBS                    PERM ONLINE       1       100        75        24
24.5
SYSTEM               PERM ONLINE       2       100        51        48  48.2
TEMP                 TEMP ONLINE       1        25        14        10  43.7
USERS                PERM ONLINE       1       300        91       208  69.5


==================== ==== ======== ===== ========= ========= ========= =====

Total:                                 5       525       232       292  55.7

(output is column-aligned, unlike this posting).

orastat has been downloaded hundreds of times by many companies. It is a perl program that runs on UX (developed on HP-UX). Those of you who have used Informix will notice a similarity to the Informix onstat command (that kind of capability I found to be lacking when I converted from Informix to Oracle.)

Enjoy,
Bill Received on Sat Dec 07 2002 - 17:44:49 CST