Categories: DM & Database

Oracle’s Database Dictionary Views

These SQL commands are related with Oracle’s data dictionary and can be used to get information about different database objects.

Select statement – To find out the oracle’s views:

This select statement retrieves views from Oracle’s data dictionary.

SELECT * FROM DICT;

Select statement – To find out the tables under your schema:

SELECT * FROM TAB;

There are several categories of dictionary views like “ALL”, “GV$”, “V$”, “USER”, “DBA”, “SYS” etc. If you have proper privileges, you can get to know about the databases, schemas, tablespace, segments, extents, blocks, partitions and so on. Here are some of the important views, that a data modeler has to know about. The following information has been sourced from www.oracle.com. For details, please go to their official website www.oracle.com.

Prefix the following views with “Select * from ” to see the results specified under the description column.

Important Data Dictionary Views:

View Name	Description
ALL_USERS	Information about all users of the database
USER_OBJECTS	Objects owned by the user
USER_DB_LINKS	Database links owned by the user
USER_TABLES	Description of the user’s own relational tables
USER_COL_COMMENTS	Comments on columns of user’s tables and views
USER_VIEWS	Description of the user’s own views
USER_VIEWS	Description of the user’s own views
USER_MVIEWS	All materialized views in the database
USER_SYNONYMS	The user’s private synonyms
USER_TRIGGERS	Triggers owned by the user
USER_PROCEDURES	Description of the users own procedures
USER_SEQUENCES	Description of the user’s own SEQUENCEs
USER_COL_PRIVS	Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE	All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD	Grants on columns for which the user is the grantee
USER_ROLE_PRIVS	Roles granted to current user
USER_CONSTRAINTS	Constraint definitions on user’s own tables
USER_CONS_COLUMNS	Information about accessible columns in constraint definitions
USER_DEPENDENCIES	Dependencies to and from a users objects
USER_INDEXES	Description of the user’s own indexes
USER_IND_COLUMNS	COLUMNs comprising user’s Indexes and Indexes on user’s TABLES
USER_SOURCE	Source of stored objects accessible to the user