orastat Home

Home | Index/DBAmon Doc. | DBAmon Version/Change History | DBAmon Event/Error Doc. | What DBAmon Monitors | DBAmon Download | Free Oracle Tool: orastat | Request Support


Updated Desc.
2010/04/16 New DB Threshold commands: -emth, -emac, -emah + New -unl, -ser + New way to connect from OS userids other than "oracle".


To download, click on the orastat Download Form.

The download will consist of one big file (a Perl program) called orastat. After downloading, you will need to save it in a public bin directory (maybe /usr/local/bin) as orastat. You also may have to change the first line (#!) to point to YOUR Perl5 executable (if /usr/bin/env perl doesn't work on your system) Note that orastat requires Perl Version 5. You will also need to make it executable (chmod 775 orastat). After doing these steps, invoke orastat by running (from the OS UX shell): orastat. If you don't supply anything after the word orastat, you will see all of the available orastat subcommands.

If you want to receive EMail when orastat enhancements are made, send an EMail to: DBAmon Support.


orastat is a UX-based Command-Line tool which simplifies performing simple Oracle DBA tasks. In reality it is just a BIG Perl program which issus SQL commands via sqlplus, and reformats the output to the OS command line.

Oracle provides a wealth of information in the Data Dictionary, but it's never really had a command line interface to extract that data (other than sqlplus, but you have to write your own SQL). orastat effectively provides the canned SQL to extract data from the data dictionary to do common DBA tasks. For example, if you need to know how full the tablespaces on your database are, without orastat, you would have to:

  1. Invoke sqlplus
  2. Manually enter (or retrieve previously stored) SQL:
    select    a.tablespace_name as tablespace_name,
              a.sum_bytes as bytes_avail,         
              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 run: orastat -ts from the UX shell, and the output would look like:

2010/04/02-13:51:25 orastat | oraver=10.2. oraver_short=10.2 oraver_num=10.2 sqlcmd=sp arg=-ts arg2=
2010/04/02-13:51:25 orastat | ORACLE_SID=mydb ORACLE_HOME=/opt/oracle/oracle/product/10.2.0/db_1 - HP-UX myserver B.11.11 U 9000/800 830940640 unlimited-user license
2010/04/02-13:51:25 orastat | Version=3.46 Host=myserver Company=dec

PermanentTablespace  Type Status   ExtentMgmt AllocTyp NumDF  Size(MB)  Free(MB)  Used(MB)    Pct
-------------------- ---- -------- ---------- -------- ----- --------- --------- --------- ------
DBATOOLS             PERM ONLINE   LOCAL      UNIFORM      1       500       336       164  32.80
EXAMPLE              PERM ONLINE   LOCAL      SYSTEM       1       150       109        40  26.96
MGMT_ECM_DEPOT_TS    PERM ONLINE   LOCAL      SYSTEM       1       100        71        28  28.44
MGMT_TABLESPACE      PERM ONLINE   LOCAL      SYSTEM       1      4000       799      3200  80.01
SYSAUX               PERM ONLINE   LOCAL      SYSTEM       1       650       214       435  66.95
SYSTEM               PERM ONLINE   LOCAL      SYSTEM       1       750       171       578  77.08
UNDOTBS1             UNDO ONLINE   LOCAL      SYSTEM       1       960       908        51   5.35
USERS                PERM ONLINE   LOCAL      SYSTEM       1       500       457        42   8.44
==================== ==== ======== ========== ======== ===== ========= ========= ========= ======
Total: (8 TS)                                              8      7610      3070      4539  59.66

2010/04/02-13:51:26 orastat | Found 8 tablespace(s)

Note: This DB also has 1 8i+ Temporary TS(s) listed in dba_temp_files - invoking orastat -tt

TemporaryTablespace  Type Status   ExtentMgmt AllocTyp NumDF  Size(MB)  Free(MB)  Used(MB)    Pct
-------------------- ---- -------- ---------- -------- ----- --------- --------- --------- ------
TEMP                 TEMP ONLINE   LOCAL      UNIFORM      1       186       183         3   1.61
==================== ==== ======== ========== ======== ===== ========= ========= ========= ======
Total:                                                     1       186       183         3   1.61

2010/04/02-13:51:26 orastat | NOTE: These numbers are COMPLETELY ACCURATE. They show
2010/04/02-13:51:26 orastat |       v$sort_usage and dba_temp_file data.

What you can do with orastat:

Since orastat is run from the OS shell and all output is written to STDOUT, you can obviously use OS command like grep on orastat output.

For example:

orastat Commands:

Here are the orastat commands that are currently available:

2008/02/29-08:16:11 orastat | oraver=9.2.0 oraver_short=9.2 oraver_num=9.2 sqlcmd=sp arg=-- arg2=
2008/02/29-08:16:11 orastat | ORACLE_SID=LARRY ORACLE_HOME=/opt/oracle/product/9.2.0 - HP-UX bigdog B.11.00 U 9000/800 1614329373 unlimited-user license 
2008/02/29-08:16:11 orastat | Version=3.40 Host=bigdog Company=???
orastat | Version 3.40 Usage:
   --    View This Help File
   -     Check for PMON Running, Show Oracle Version, Instance Status
   -ab   List Archive Log RMAN Backups [Option Archive Log Sequence Number]
   -ad   List Archive Destinations (v$archive_dest)
   -af   List Archive Log Directory Contents
   -al   Show All Archived Logs 
   -amm  AMM (Automatic Memory Management) (sga_target) Stats
   -an   Analyze Table COMPUTE STATISTICS - 'orastat -an TABLE-OWNER.TABLE-NAME'
   -ar   Show Current DB Activity Rate
   -asm  ASM Details & Status
   -asm_dg  ASM Diskgroups
   -asm_dk  ASM Disks
   -asm_fi  ASM Files
   -asm_op  ASM Operations
   -au   Show DB Audit Status
   -av   Archive Log Volume - [D/H] [# of days]
   -az   Show Current DB Activity Rate - Log to /opt/oracle/adm/cronlog/db_activity_rate_.txt
   -ba   List Contents of dbamon.backup_age Table
   -bb   List Summary of all RMAN backup pieces - from v$backup_piece
   -bc   List Contents of buffer pool
   -bd   Datafile backup mode status
   -bi   List RMAN backup inventory -- [ALL|SINCE|BETWEEN]
   -bm   List Tablespaces and Datafiles in Backup Mode (from v$backup)
   -bo   Buffer Cache Overview (Many Reports)
   -bp   List running RMAN backup sessions (if any)
   -br   Backups: List all media written to since the last RMAN LVL0 backup
   -brm  Backup Media: List all backup media
   -bs   Create DDL to take datafiles that are in backup mode out of backup mode
   -c    Configuration: View init.ora File
   -cb   Controlfile Backup: Trace and Binary
   -cc   Client Connections
   -cd   Controlfile Details (Sections)
   -ce   Configuration: Edit 'vi' init.ora File
   -cf   List Control Files
   -ck   List Time of Last Checkpoint (from v$datafile_headers)
   -cm   Configuration: View Contents of v$parameter with modifiable flags
   -cp   Configuration: View Contents of v$parameter
   -ct   Coalesce TEMP tablespace
   -cv   Generate syntax to recompile INVALID Objects
   -da   List Datafiles - Autoextend details
   -dc   List Datafiles in 'cp' commands to copy all datafiles elsewhere
   -dd   Reproduce Object DDL (9i+ Only)
           Table:      orastat -dd TABLE SCOTT DEPT
           Index:      orastat -dd INDEX SCOTT DEPT_X
           Tablespace: orastat -dd TABLESPACE "" TSNAME
           User:       orastat -dd USER "" SCOTT
           DBLink:     orastat -dd USER "" SCOTT
   -de   List All Datafiles, Online Redo Logs and Control Files (For Destroying a DB)
   -df   List Datafiles - Optional FSCHECK 2nd parm to bdf filesystem
   -df3  List Datafiles - from v$datafile
   -dh   List Datafiles and their 'Used Blocks' High-Water-Mark - Creates RESIZE commands
   -di   Datafiles Contents (extents) by Block address - Must supply DF#
   -dn   A Better and Faster version of -dh
   -dp   Database properties (from DATABASE_PROPERTIES)
   -dst  DST Patch checking
   -du   List Datafiles where UNRECOVERABLE_CHANGE# is not null
   -ec   Configuration: Edit 'vi' config.ora File
   -er   Display contents of DBA_ERRORS
   -ev   Select from v$system_event
   -ex   Select from plan_table
   -fm   Display hours since file modification time - Requires ARG
   -fra  Flash Recovery Area Status
   -fs   Free Space in Each Datafile
   -ft   List 'Fast Start' Transactions being rolled back
   -hd   Hot Tables - With Datafile Name
   -ht   Hot Tables
   -in   List Indexes
   -iv   List INVALID Objects
   -l    List Archive Log Status
   -la   Latch Details (from v$latch)
   -lf   List Redo Log Files
   -lg   Longops: Long Operation Progress
   -lh   Lock Holders/Waiter
   -li   List Resource Limits (v$resource_limit)
   -lk   Locks - Current TX (Non-Row) Locks
   -ln   List DB Links
   -lo   List Current Table Locks
   -ls   Listener Status
   -lv   List LVOL's / Usage
   -m    View Last 20 Lines Of Alert Log
   -ma   Cat entire Alert Log
   -mf   Tail -90f Alert Log
   -mr   Alert log - reformatted - Last 20 lines
   -mr a Alert log - reformatted - entire file
   -mr v Alert log - reformatted - 'vi' entire file
   -mt   MTS Statistics
   -mv   'vi' (read-only mode) Of Alert Log
   -nf   List Objects whose NEXT EXTENT will not fit in the tablespace
   -nl   List NOLOGGING Tables and Indices
   -ob   Objects - With Decimal and Hex Object ID
   -op   OPS: View V$PING - Lock Conversions
   -pb   Performance: Current Data Block Buffer Hit Ratio - RIGHT NOW
   -pc   Password Check: Check for accounts where username=password
   -pd   Performance: View Data Block Buffer Hit Ratio
   -pf   Performance: View Total Cumulative Free List Waits
   -ph   Performance: Hot Blocks - Block With Latch Sleeps
   -pi   Performance: View Histogram of Datafile I/O
   -pj   Performance: I/O Distribution By Filesystem
   -pk   Performance: I/O Distribution By Tablespace
   -pp   Performance: I/O Service Times
   -pr   List Profiles
   -ps   OPS (Parallel Server) Status
   -pw   Performance: Show segment names for tables with buffer waits
   -qs   SQL Statment Count
   -ra   List Rollback Segment Activity
   -rac  RAC Status (the same as orastat -ps)
   -rb   List Rollback Segments
   -rc   List And SHRINK All Rollback Segments
   -rd   List And ALTER MAXEXTENTS UNLIMITED All Rollback Segments
   -rf   View Timestamps from V$RECOVER_FILE
   -rg   View DBA_REGISTRY
   -rh   List REDO Logs - History
   -rl   List REDO Logs - Files
   -ro   List Roles
   -rp   RMAN Long Operation Progress
   -rs   List REDO Logs - Status
   -ru   Rollback Usage - By Session
   -sa   Session Statistics - All Sessions
   -sb   Standby DB - Show log gaps
   -sc   Sessions - By Session CPU Time
   -sd   Sessions - Details - Sessions, Running SQL and Waits
   -se   Sessions
   -sg   List SGA Usage
   -sh   Shared Pool Usage & Tuning Recommendations
   -sl   SELECT * from table - name supplied as 2nd parameter
   -sn   SNAPSHOT - Run systemstate trace 3 times (for Oracle diagnostics)
   -so   List sorts by user
   -sp   List StatsPack Snapshot Data
   -spf  Display SPFILE (if any)
   -sq   Run SQL - Pass SQL as argument in single quotes
   -sr   View Running SQL - 'orastat -sr SESSION_NUMBER'
   -ss   List default storage clause for all TABLESPACES
   -st   System Statistics - from v$sysstat
   -su   System Utilization
   -sw   Session Wait Statistics
   -sy   List All Granted System Privileged
   -sz   Session statistics for one session - 2nd parm is SID
   -ta   List All Tables - From DBA_SEGMENTS (Name, TS, Size, Extents, Maxextents) - 2nd Arg 'ALL' to include SYS* tables
   -tb   Count Tables - By Schema
   -tc   Create SQL to count all rows in all permanent tables
   -td   describe table - name of table supplied as 2nd parameter
   -ti   Table Info - 'orastat -ti TABLE-OWNER.TABLE-NAME [-count]'
   -tj   Datafile Space Allocated - By Filesystem
   -tm   List Temporary Segments
   -tp   List Objects (from DBA_SEGMENTS) By BLOCKS and BUFFER_POOL - Show buffer pool stats
   -tq   Tablespaces - Details and Defaults
   -tr   List Transactions
   -ts   List Tablespaces
   -tt   List 8i+ TEMPORARY Locally Managed Tablespaces
   -tu   Temp space usage by user
   -tz   List Tables - From DBA_TABLES - Shows ANALYZE data/timestamp
   -ua   List USERS with incorrect default/temp tablespace - Creates SQL to ALTER USERS
   -ub   List the byte count of data, by User
   -ud   List Users With ORADBA
   -ug   List Users Table Grants (Much Output) - 2nd Parm is optional grantee name
   -un   Display 9i UNDO Statistics
   -up   User Password Information
   -us   List All Users
   -ut   List the byte count of data, by User and Tablespace
   -v    List Oracle version and whether it is 32-bit or 64-bit
   -vs   List All Views
   -vw   Count Views - By Schema
   -wr   Wait Reasons - By User and Wait Type
   -ws   Wait Stats - from v$waitstat
   -zr   List contents of DBAMON.STANDBY_REBUILD

???-Specific Commands:
   -cfbl List trace controlfile backup with create timestamp > [numeric timestamp]


orastat is distributed on a freeware, as-is basis. It is not guaranteed to perform any purpose at all. Look at the code before running it. We will not be responsible for any problems caused by orastat. orastat is written in Perl language. If Perl ever becomes a licensed language, we will not be responsible for providing any such license.

Sample Output:

orastat -cp
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1
orastat | Active Parameters (from v$parameter)
Parm                                     Value                               Desc.
======================================== =================================== ==============================
O7_DICTIONARY_ACCESSIBILITY              TRUE                                Version 7 Dictionary Accessibility Suppo
_controlfile_enqueue_timeout             1800                                control file enqueue timeout in seconds 
allow_partial_sn_results                 FALSE                               allow partial results when processing gv
always_anti_join                         NESTED_LOOPS                        always use this anti-join when possible 
always_semi_join                         standard                            always use this semi-join when possible 
aq_tm_processes                          0                                   number of AQ Time Managers to start     
orastat -df
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1
Tablespace       Datafile Datafile                                                Datafile  FS Free  
Name             Num      Path                                                    Size(MB)  Space(MB)
---------------- -------- ------------------------------------------------------- --------- ---------
PSAPBTABD              73 /oracle/PD1/sapdata9/btabd_11/btabd.data11                   2039      5584
""                     72 /oracle/PD1/sapdata1/btabd_10/btabd.data10                    699      1663
""                     67 /oracle/PD1/sapdata7/btabd_8/btabd.data8                     2039      2097
""                     40 /oracle/PD1/sapdata2/btabd_6/btabd.data6                     1000      2076
""                     31 /oracle/PD1/sapdata8/btabd_3/btabd.data3                     2000      2887
""                     14 /oracle/PD1/sapdata7/btabd_9/btabd.data9                     2039      2097
orastat -ts
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1

Tablespace           NumDF  Size(MB)  Free(MB)  Used(MB)   Pct
-------------------- ----- --------- --------- --------- -----
PSAPBTABD               11     15319      2800     12519  81.7
PSAPBTABI                9     10594      2138      8456  79.8
PSAPCLUD                 5      2636       735      1900  72.1
PSAPCLUI                 1       500       120       379  75.9
PSAPDDICD                3       939       411       528  56.3
PSAPDDICI                2       525       266       259  49.4
orastat -pd
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1
Data Block Buffer Hit Ratio Data

 Consistent Gets:      -1263179968
 DB Block Gets:          18559496
 Physical Reads:        164901273
 Hit Ratio:                 94.59%

 DB Block Size:              8192
 DB Block Buffers:          23000
orastat -pi
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1
                                                                            Pct Of
                                                       1000's 1000's 1000's DB I/O
Datafile                                                Reads Writes    R+W  Total
====================================================== ====== ====== ====== ======
/oracle/PD1/sapdata2/btabi_2/btabi.data2                63345      9  63354 38.08% ****      
/oracle/PD1/sapdata2/btabd_1/btabd.data1                32739      5  32744 19.68% **        
/oracle/PD1/sapdata5/btabi_4/btabi.data4                20648     54  20702 12.44% **        
/oracle/PD1/sapdata5/btabi_1/btabi.data1                17678    119  17797 10.70% **        
/oracle/PD1/sapdata5/btabi_5/btabi.data5                 9444     17   9462  5.69% *         
orastat -sg
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1
orastat | SGA Status

SGA Pool             Size(Bytes) Size(MB)
==================== =========== ========
Shared Pool (Total)    157954480   157.95
Shared Pool (Free)      82300800    82.30  47.9% Full
DB Block Buffer        188416000   188.42
Redo Log Buffer          1048576     1.05
Fixed SGA                  52200     0.05
orastat -ta
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1

All Tables (From DBA_SEGMENTS)

Owner.Table                               TableSpace   Blocks     MB Xtents  MaxXtnt
========================================= ============ ====== ====== ====== ========
DBAMON.BACKUP_AGE                         PSAPUSER1D        4      0      3  (Unlim)
OPS$MA2ADM.SAPUSER                        PSAPUSER1D        2      0      1      121
OPS$PD1ADM.SAPUSER                        PSAPUSER1D        2      0      1  (Unlim)
SAPR3./SAPSMOSS/ADDID                     PSAPPOOLD         2      0      1      300
orastat -us
orastat | ORACLE_SID=PD1 ORACLE_HOME=/oracle/PD1

All Users

User       Password   Profile    Dflt. TS     Temp. TS       Created  
========== ========== ========== ============ ============== ===================
DBAMON     (Set)      DEFAULT    PSAPUSER1D   PSAPTEMP       15-JUL-00           
DBSNMP     (Set)      DEFAULT    SYSTEM       SYSTEM         11-MAR-00           
DBSPI      (Set)      DEFAULT    PSAPUSER1D   PSAPTEMP       01-NOV-99           
OPS$MA2ADM (Set)      DEFAULT    PSAPUSER1D   PSAPTEMP       11-MAR-00           

This Document: http://dbamon.com/orastat/index.shtml