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 NameDescription
ALL_USERSInformation about all users of the database
USER_OBJECTSObjects owned by the user
USER_DB_LINKSDatabase links owned by the user
USER_TABLESDescription of the user’s own relational tables
USER_COL_COMMENTSComments on columns of user’s tables and views
USER_VIEWSDescription of the user’s own views
USER_VIEWSDescription of the user’s own views
USER_MVIEWSAll materialized views in the database
USER_SYNONYMSThe user’s private synonyms
USER_TRIGGERSTriggers owned by the user
USER_PROCEDURESDescription of the users own procedures
USER_SEQUENCESDescription of the user’s own SEQUENCEs
USER_COL_PRIVSGrants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADEAll grants on columns of objects owned by the user
USER_COL_PRIVS_RECDGrants on columns for which the user is the grantee
USER_ROLE_PRIVSRoles granted to current user
USER_CONSTRAINTSConstraint definitions on user’s own tables
USER_CONS_COLUMNSInformation about accessible columns in constraint definitions
USER_DEPENDENCIESDependencies to and from a users objects
USER_INDEXESDescription of the user’s own indexes
USER_IND_COLUMNSCOLUMNs comprising user’s Indexes and Indexes on user’s TABLES
USER_SOURCESource of stored objects accessible to the user

Leave a Reply

Your email address will not be published. Required fields are marked *