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 |