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
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' from DBA_SEGMENTS 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
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
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). ]