Oracle DBA Commonly Used Views

Below is a list of some of the views DBAs use to monitor a database.  A DBA can see what columns a particular view provides by using the describe view SQL command.  Then you can limit which columns appear to the ones of interest at the moment.  You can also limit the rows to the specific things of interest.  An example might be:

select Owner, Tablespace_name, Segment_Name, Extents as '# Extents',
       Max_Extents as 'Max Extents', (Extents/Max_Extents)*100 as '% used'
where Owner not in ('SYS', 'SYSTEM')
  and Segment_type not in ('ROLLBACK', 'DEFERRED ROLLBACK', TEMPORARY')
order by Tablespace_Name, Owner;

Many of the DBA_ views shown in the table below have corresponding USER_ views that show similar information but for the current user (schema) only.  Likewise, the various V$ views, which show information about the current instance have matching GV$ views, which show information about all instances.

The table below lists the views in three general categories, resource managment views, performance monitoring views, and security related views).  Within each group the views are not listed in any particular order however.

System Resource Views
USER_ERRORS Displays the errors found in views, stored procedures, and other objects.
DBA_TABLESPACES Provides information about each tablespace in the database.
DBA_USERS Provides information about each user in the database.
DBA_SOURCE Shows the source code for stored functions, triggers, and Java objects
DBA_DATAFILES Gives information about the datafiles.
DBA_SEGMENTS Describes the storage usage for the segments (the part of a datafile that contains the physical storage for a database object).
DBA_EXTENTS More detailed information on space used for segments.  (Too many extents per segment can take hours if dropping the associated table.  You can also check the next extent size to make sure you have sufficient free space.)
DBA_UNDO_EXTENTS Used to monitor the space used for automatic undo management.  (If not using AUM you will have rollback segments instead, and the view to use for that is DBA_ROLLBACK_SEGS.)
DBA_TABLES Lets the DBA view information about any table.
DBA_TAB_COLUMNS Provides detailled information about each column of a table.
DBA_INDEXES Show information about table indexes; helps when trying to decide if some index is useful or not.  A related view is DBA_IND_COLUMNS which tells which columns of a table are indexed.
DBA_CONSTRAINTS Lists the restristions that have been placed on the contents of columns of tables and views.  More detailed information comes from the related view DBA_CONS_COLUMNS.
Shows the current versions of all installed components.
Performance Monitoring Views
V$SYSSTAT Statistics such as the amount of data changed and the number of transactions executed.
V$SQL The actual SQL commands recently executed, how many time it was executed and how much CPU time it took.
V$SESSTAT Shows resources consumed per session.  (Related: V$SESSION_WAIT)
V$FILESTAT The number of reads and writes per file, and time spent on disk I/O.
V$DATAFILE Lists the file number and name for each DB file.  In many views and tables Oracle identifies files by the number rather than name.  (Related: V$DATAFILE_HEADER)
V$DATABASE General information about the database
V$DBFILE Shows file name to numbers
V$FIXED_TABLE Shows the names of all dynamic tables and views.
V$INSTANCE General information about each instance.
V$PARAMETER Shows all the initializatino parameters and their current values.
V$SGA Summary statistics for the use of space in the SGA.
V$TEMPFILE Shows Statistics for locally-managed temporary tablespace files.
Security Related Views
DBA_USERS Lists users who can access a database.


[ Much of the information in this table comes from the book Oracle9i DBA 101 by Theriault, Carmichael, and Viscusi.  ©2002 by McGraw-Hill / Osborne (the Oracle Press imprint). ]