List of system objects and what they are for the Oracle Database.
- Catalog of the Oracle Data Dictionary
- List of Oracle System Tables and what they contain
- Static Dictionary Views
Data Dictionary
There's a view of all the data dictionary views in oracle called DICTIONARY. So you can query it like so:
select TABLE_NAME, COMMENTS from DICTIONARY order by TABLE_NAME;
This includes comments about most of the views which is really handly. Almost all the views use plurals (ALL_TABLES not ALL_TABLE)
To find all the tables associated with a schema, query ALL_TABLES and specify the appropriate OWNER e.g. SYSADM.
Dynamic Performance Views
The dynamic performance views (v$) are built on the virtual tables ($x). The views (v$) are much more useful than the virtual tables ($x). To get a listing of all them use the following query:
select NAME from V$FIXED_TABLE where TYPE = 'VIEW';
Most useful views (from
- v$database Details about the nature and state of the database
- v$transaction What transactions are currently uncommitted
- v$session What users have logged on
- v$controlfile Names and locations of all control files
- v$logfile Names and locations of all online redo logs
- v$datafile Names and locations of all data files
- v$instance Details about the nature and state of the instance
- v$sqltext What queries are users issuing
- v$process What background and server processes are running
Global dynamic performance views have the prefix gv$. They are viewing across all instances when using a real application cluster (RAC) configuration.
