Data dictionary tables are important part of Oracle database. We can say it is one kind of Metadata of all Oracle database object related information. Data dictionary is a read-only set of tables and views that tells us the story of all objects in database.
The owner of data dictionary is SYS user. Data dictionary components are stored in SYSTEM tablespace principally. However, after release of Oracle Database 10g, some components are stored in SYSAUX tablespace too.
The information contained in data dictionary views ranges from general database information to the space allocated to a particular object in database.
In short, data dictionary view has information about:
- Default value of columns
- Tablespaces, users and auditing
- Grants and privileges
- Database objects (table, view, package, procedures etc.)
- Sessions and instances
- Constraints and Indexes
and much more…
ALL, USER and DBA Data Dictionary Views
There are three types of views in data dictionary
Views that starts with USER shows information pertaining to that particular user with which you are logged in. You will not get information about objects on which you have access, but are owned by other users.
SELECT * FROM user_objects;
This query returns objects owned by user SCOTT if you are logged in by SCOTT. The objects on which you have access to but are owned by TOM user will not be viewed by this query.
Views that start with ALL shows information pertaining to objects owned by logged in user as well as information about the objects that are owned by different user but are accessible to logged in user.
SELECT * FROM all_objects;
This query will return objects owned by SCOTT as well those objects owned by TOM and other users, but are accessible to SCOTT.
Views that start with DBA are at top level among the three. They are direct window to all objects of the database with unrestricted access to them. Therefore DBA views are available only with DBAs – safe from the security point of view.
SELECT * FROM dba_objects;
This query will fetch information about all objects of all users.
There are more than hundred data dictionary views. DICTIONARY view contains all the information about data dictionary views and tables.
SELECT * FROM dictionary;
Frequently Used Data Dictionary Tables and Views
Here, I have pointed out some views that could be worthwhile to know for programmers or developers who deal with Oracle database frequently:
|ALL_ARGUMENTS||Arguments in object accessible to the user|
|ALL_CATALOG||All tables, views, synonyms, sequences accessible to the user|
|ALL_COL_COMMENTS||Comments on columns of accessible tables and views|
|ALL_CONSTRAINTS||Constraint definitions on accessible tables|
|ALL_CONS_COLUMNS||Information about accessible columns in constraint definitions|
|ALL_DB_LINKS||Database links accessible to the user|
|ALL_ERRORS||Current errors on stored objects that user is allowed to create|
|ALL_INDEXES||Descriptions of indexes on tables accessible to the user|
|ALL_IND_COLUMNS||COLUMNs comprising INDEXes on accessible TABLES|
|ALL_LOBS||Description of LOBs contained in tables accessible to the user|
|ALL_OBJECTS||Objects accessible to the user|
|ALL_OBJECT_TABLES||Description of all object tables accessible to the user|
|ALL_SEQUENCES||Description of SEQUENCEs accessible to the user|
|ALL_SNAPSHOTS||Snapshots the user can access|
|ALL_SOURCE||Current source on stored objects that user is allowed to create|
|ALL_SYNONYMS||All synonyms accessible to the user|
|ALL_TABLES||Description of relational tables accessible to the user|
|ALL_TAB_COLUMNS||Columns of user's tables, views and clusters|
|ALL_TAB_COL_STATISTICS||Columns of user's tables, views and clusters|
|ALL_TAB_COMMENTS||Comments on tables and views accessible to the user|
|ALL_TRIGGERS||Triggers accessible to the current user|
|ALL_TRIGGER_COLS||Column usage in user's triggers or in triggers on user's tables|
|ALL_TYPES||Description of types accessible to the user|
|ALL_UPDATABLE_COLUMNS||Description of all updatable columns|
|ALL_USERS||Information about all users of the database|
|ALL_VIEWS||Description of views accessible to the user|
|ALL_ALL_TABLES||Description of all object and relational tables accessible to the user|
|ALL_DIRECTORIES||Description of all directories accessible to the user|
|ALL_MVIEWS||All materialized views in the database|
|ALL_NESTED_TABLES||Description of nested tables in tables accessible to the user|
|ALL_VARRAYS||Description of varrays in tables accessible to the use|
|ALL_OPERATORS||All operators available to the user|
|ALL_TAB_PRIVS||Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee|
|DICTIONARY||Description of data dictionary tables and views|
|DICT_COLUMNS||Description of columns in data dictionary tables and views|
|GLOBAL_NAME||global database name|
|NLS_DATABASE_PARAMETERS||Permanent NLS parameters of the database|
|NLS_INSTANCE_PARAMETERS||NLS parameters of the instance|
|NLS_SESSION_PARAMETERS||NLS parameters of the user session|
|ROLE_TAB_PRIVS||Table privileges granted to roles|
|SESSION_PRIVS||Privileges which the user currently has set|
|SESSION_ROLES||Roles which the user currently has enabled.|
|SYSTEM_PRIVILEGE_MAP||Description table for privilege type codes. Maps privilege type numbers to type names|
|TABLE_PRIVILEGES||Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee|
|TABLE_PRIVILEGE_MAP||Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names|
|DBA_ROLES||All Roles which exist in the database|
|DBA_ROLE_PRIVS||Roles granted to users and roles|
|DBA_TABLESPACES||Description of all tablespaces|
|DBA_TS_QUOTAS||Tablespace quotas for all users|
Hope you like the article. We highly appreciates comments and feedback.