Oracle System Objects

List of system objects and what they are for the Oracle Database.

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.

Resources

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License