orastat Home

Home | Index / Documentation | Change History | Event / Error Documentation |
What DBAmon Monitors | Free Oracle Tool: orastat | Request Support

Overview:

orastat is a Unix/Linux based tool which simplifies performing simple Oracle 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 svrmgrl or sqlplus
  2. Manually enter (or retrieve from where you previously stored) SQL: 
    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 run: orastat -ts and the output would look like: 
2001/04/02-08:39:32 orastat | ORACLE_SID=DBA ORACLE_HOME=/opt/oracle/product/8.1.6
2001/04/02-08:39:32 orastat | Version=1.36 Host=bigdog

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
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]
--------------------------------------------------------------------

Disclaimer:

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.

Download:

To download, click on the Orastat Download Form.

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 (orastat requires Perl Version 5). You will need to make it executable (chmod 775 orastat).

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

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           
...
DBAmon.com
This Document: http://dbamon.com/orastat/index.shtml