How to use Oracle Data Dictionary?

Data Dictionary is the metadata of the oracle database. It contains all informations about the database objects. There are sets of views in the Data Dictionary. They are in the SYSTEM tablespace and have prefixes.

Prefix	Description
USER	User’s schema
ALL	User can access
DBA	Database Administators view

Examples;

Returns all objects and object states of your schema:

SELECT object_name, object_type, status FROM USER_OBJECTS;

Returns all objects and object states to which you have access:

SELECT object_name, object_type, status FROM ALL_OBJECTS;

Returns all objects and object states of the database per owner basis:

SELECT owner, object_name, object_type, status FROM SYS.DBA_OBJECTS;

There are also performance views in the Data Dictionary which prefixed V$.

Following statement shows who is connected to the database:

SELECT sid, serial#, username, osuser, machine from V$SESSION where username is not NULL;

Static Data Dictionary Views can be listed along with comments in dict:

SELECT * from DICT order by table_name;