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