Skip to main content

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;

Comments

Popular posts from this blog