Basic & Important views in For Oracle DBA
Best way to understand the views (DBA and v$) is to log onto sqlplus prompt as sysdba and fire the command desc view_name. It will give you the names of all the columns in that particular view which you describe (desc stands for describe). Then you can run your select command to query the information you need i.e.: select name, open_mode, database_role from v$database;
DBA_OBJECTS Describes views, stored procedures, and other objects.
DBA_DATA_FILES Describes all datafiles in tablespaces.
DBA_TEMP_FILES Describes all datafiles in Temp tablespace.
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.
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 Shows general information about each instance.
V$PARAMETER Shows all the initializatin parameters and their current values.
V$SGA Summarises the statistics for the use of space in the SGA.
V$TEMPFILE Shows Statistics for locally-managed temporary tablespace files.
User/ Security Related Views
DBA_TAB_PRIV
ALL_TAB_PRIVS_MADE
DBA_TAB_PRIVS_MADE
DBA_USERS Lists users who can access a database.
DBA_PROFILES
DBA_ROLES
DBA_ROLE_PRIVS
DBA_SYS_PRIVS
DBA_TAB_PRIVS
DBA_TS_QUOTA
DBA_VIEWS