Born to Thrive

One of the exam requirements is "Use the data dictionary views to research data on your objects". Knowing dictionary views and tables is essential as dictionary data contains information about the database (concept of metadata) and its state. They are used for management, tunning and monitoring of the database. The data dictionary is created when the Oracle database is created. It is owned by the SYS user, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace in Oracle Database 10g.The data dictionary views come in three main flavors:

• User views - views with information about the user's objects
• All views - views with information about objects accessible to the user
• DBA views - views with information of all objects in the database

Below is a list of dictionary views with description of their purpose. The list contains probably more dictionary views than it is required for this exam, but it's better be safe than sorry.

• ALL_ALL_TABLES - Description of all object and relational tables accessible to the user
• ALL_ASSOCIATIONS - All associations available to the user
• ALL_CATALOG - All tables, views, synonyms, sequences accessible to the user
• ALL_COLL_TYPES - Description of named collection types accessible to the user
• ALL_COL_COMMENTS - Comments on columns of accessible tables and views
• ALL_COL_PRIVS - Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
• ALL_COL_PRIVS_MADE - Grants on columns for which the user is owner or grantor
• ALL_COL_PRIVS_RECD - Grants on columns for which the user, PUBLIC or enabled role is the grantee
• ALL_CONSTRAINTS - Constraint definitions on accessible tables
• ALL_CONS_COLUMNS - Information about accessible columns in constraint definitions
• ALL_CONS_OBJ_COLUMNS - List of types an object column or attribute is constrained to in the tables accessible to the user
• ALL_DB_LINKS - Database links accessible to the user
• ALL_DEPENDENCIES - Dependencies to and from objects accessible to the user
• ALL_DIRECTORIES - Description of all directories accessible to the user
• ALL_ERRORS - Current errors on stored objects that user is allowed to create
• ALL_EXTERNAL_LOCATIONS - Description of the external tables locations accessible to the user
• ALL_EXTERNAL_TABLES - Description of the external tables accessible to the user
• ALL_FILE_GROUPS - Details about file groups
• ALL_INDEXES - Descriptions of indexes on tables accessible to the user
• ALL_INDEXTYPES - All indextypes available to the user
• ALL_IND_COLUMNS - Columns comprising indexes on accessible tables
• ALL_IND_EXPRESSIONS - Functional index expressions on accessible tables
• ALL_JOIN_IND_COLUMNS - Join Index columns comprising the join conditions
• ALL_LIBRARIES - Description of libraries 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_NESTED_TABLE_COLS - Columns of nested tables
• ALL_OBJECTS - Objects accessible to the user
• ALL_RULES - Rules seen by the user
• ALL_SEQUENCES - Description of SEQUENCEs accessible to the user
• ALL_SYNONYMS - All synonyms for base objects accessible to the user and session
• ALL_TABLES - Description of relational tables accessible to the user
• ALL_TAB_COLS - Columns of user's tables, views and clusters
• 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_TAB_PRIVS - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
• ALL_TAB_PRIVS_MADE - User's grants and grants on user's objects
• ALL_TAB_PRIVS_RECD - Grants on objects for which the user, PUBLIC or enabled role is the grantee
• ALL_TYPES - Description of types accessible to the user
• ALL_UNUSED_COL_TABS - All tables with unused columns accessible to the user
• ALL_UPDATABLE_COLUMNS - Description of all updatable columns
• ALL_USERS - Information about all users of the database
• ALL_USTATS - All statistics
• ALL_VIEWS - Description of views accessible to the user
• CAT - Synonym for USER_CATALOG
• COLS - Synonym for USER_TAB_COLUMNS
• COLUMN_PRIVILEGES - Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
• DICT - Synonym for DICTIONARY
• DICTIONARY - Description of data dictionary tables and views
• DICT_COLUMNS - Description of columns in data dictionary tables and views
• GV$ACTIVE_INSTANCES - Synonym for GV_$ACTIVE_INSTANCES
• GV$NLS_PARAMETERS - Synonym for GV_$NLS_PARAMETERS
• GV$TIMEZONE_NAMES - Synonym for GV_$TIMEZONE_NAMES
• GV$VERSION - Synonym for GV_$VERSION
• IND - Synonym for USER_INDEXES
• NLS_INSTANCE_PARAMETERS - NLS parameters of the instance
• NLS_SESSION_PARAMETERS - NLS parameters of the user session
• OBJ - Synonym for USER_OBJECTS
• RECYCLEBIN - Synonym for USER_RECYCLEBIN
• ROLE_ROLE_PRIVS - Roles which are granted to roles
• ROLE_SYS_PRIVS - System privileges granted to roles
• ROLE_TAB_PRIVS - Table privileges granted to roles
• SEQ - Synonym for USER_SEQUENCES
• SESSION_PRIVS - Privileges which the user currently has set
• SESSION_ROLES - Roles which the user currently has enabled.
• SM$VERSION - Synonym for SM_$VERSION
• SYN - Synonym for USER_SYNONYMS
• TABLE_PRIVILEGES - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
• TABS - Synonym for USER_TABLES
• USER_ALL_TABLES - Description of all object and relational tables owned by the user's
• USER_CATALOG - Tables, Views, Synonyms and Sequences owned by the user
• USER_COL_COMMENTS - Comments on columns of user's tables and views
• 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_CONSTRAINTS - Constraint definitions on user's own tables
• USER_CONS_COLUMNS - Information about accessible columns in constraint definitions
• USER_CONS_OBJ_COLUMNS - List of types an object column or attribute is constrained to in the tables owned by the user
• USER_DB_LINKS - Database links owned by the user
• USER_DEPENDENCIES - Dependencies to and from a users objects
• USER_ERRORS - Current errors on stored objects owned by the user
• USER_EXTERNAL_LOCATIONS - Description of the user's external tables locations
• USER_EXTERNAL_TABLES - Description of the user's own external tables
• USER_FREE_SPACE - Free extents in tablespaces accessible to the user
• USER_INDEXES - Description of the user's own indexes
• USER_INDEXTYPES - All user indextypes
• USER_INDEXTYPE_ARRAYTYPES - All array types specified by the indextype
• USER_INDEXTYPE_COMMENTS - Comments for user-defined indextypes
• USER_INDEXTYPE_OPERATORS - All user indextype operators
• USER_IND_COLUMNS - columns comprising user's indexes and indexes on user's tables
• USER_IND_EXPRESSIONS - Functional index expressions in user's indexes and indexes on user's tables
• USER_JOIN_IND_COLUMNS - Join Index columns comprising the join conditions
• USER_LIBRARIES - Description of the user's own libraries
• USER_MVIEWS - All materialized views in the database
• USER_NESTED_TABLES - Description of nested tables contained in the user's own tables
• USER_NESTED_TABLE_COLS - Columns of nested tables
• USER_OBJECTS - Objects owned by the user
• USER_OBJECT_TABLES - Description of the user's own object tables
• USER_PASSWORD_LIMITS - Display password limits of the user
• USER_RECYCLEBIN - User view of his recyclebin
• USER_ROLE_PRIVS - Roles granted to current user
• USER_RULES - Rules owned by the user
• USER_SEQUENCES - Description of the user's own SEQUENCEs
• USER_SYNONYMS - The user's private synonyms
• USER_SYS_PRIVS - System privileges granted to current user
• USER_TABLES - Description of the user's own relational tables
• USER_TABLESPACES - Description of accessible tablespaces
• USER_TAB_COLS - Columns of user's tables, views and clusters
• USER_TAB_COLUMNS - Columns of user's tables, views and clusters
• USER_TAB_COMMENTS - Comments on the tables and views owned by the user
• USER_TAB_PRIVS - Grants on objects for which the user is the owner, grantor or grantee
• USER_TAB_PRIVS_MADE - All grants on objects owned by the user
• USER_TAB_PRIVS_RECD - Grants on objects for which the user is the grantee
• USER_TYPES - Description of the user's own types
• USER_UNUSED_COL_TABS - User tables with unused columns
• USER_UPDATABLE_COLUMNS - Description of updatable columns
• USER_USERS - Information about the current user
• USER_USTATS - All statistics on tables or indexes owned by the user
• USER_VIEWS - Description of the user's own views
• V$NLS_PARAMETERS - Synonym for V_$NLS_PARAMETERS
• V$NLS_VALID_VALUES - Synonym for V_$NLS_VALID_VALUES
• V$OPEN_CURSOR - Synonym for V_$OPEN_CURSOR
• V$SESSION - Synonym for V_$SESSION
• V$SQLTEXT_WITH_NEWLINES - Synonym for V_$SQLTEXT_WITH_NEWLINES
• V$SQL_PLAN - Synonym for V_$SQL_PLAN
• V$TIMEZONE_NAMES - Synonym for V_$TIMEZONE_NAMES
• V$TRANSACTION - Synonym for V_$TRANSACTION
• V$VERSION - Synonym for V_$VERSION