Learn Oracle Database and Applications Here

Important Oracle Data Dictionary Tables A Programmer Must Know

Oracle 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.

ALL, USER and DBA Oracle Data Dictionary Views

There are three types of views in data dictionary

USER

ALL

DBA

USER

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.

For example,

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.

ALL

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.

For example,

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.

DBA

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.

For example,

SELECT * FROM dba_objects;

This query will fetch information about all objects of all users.

DICTIONARY view

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 Oracle 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

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!