In-Depth Notes on Oracle Data Dictionary

Objectives

  • Describe the purposes of the Data Dictionary
  • Differentiate between the three types of Data Dictionary views
  • Write SQL SELECT statements to retrieve information from the Data Dictionary
  • Explain the use of DICTIONARY as a Data Dictionary search engine

Purpose

  • The Data Dictionary stores metadata about database objects such as procedures, functions, tables, etc.
  • It helps users keep track of the names and details of these objects, making it easier to manage database resources.

What Is the Data Dictionary?

  • Every Oracle database contains a Data Dictionary that automatically registers all database objects (e.g., tables, views, and privileges) as they are created.
  • The Data Dictionary updates automatically whenever an object is altered or dropped, serving as a master catalog of the database.

Types of Data Dictionary Views

  1. USER_* Tables

    • Contains information about objects owned by the user (e.g., USER_TABLES, USER_INDEXES).

  2. ALL_* Tables

    • Contains information about objects the user has privileges to use, including owned objects (e.g., ALL_TABLES, ALL_INDEXES).

  3. DBA_* Tables

    • Normally restricted to Database Administrators, this view contains information about all objects in the database (e.g., DBA_TABLES, DBA_INDEXES).

Reading the Dictionary

  • You can use SQL commands to view information from the Data Dictionary and retrieve data using the following commands:
    • DESCRIBE to view structure information (e.g., DESCRIBE ALL_TABLES).
    • SELECT to retrieve specific data.
Examples of SELECT Statements:
  • To see the names and owners of all tables:
  SELECT table_name, owner FROM ALL_TABLES;
  • To view all objects owned by the user:
  SELECT object_type, object_name FROM USER_OBJECTS;
  • To count the number of objects by type owned by the user:
  SELECT object_type, COUNT(*) FROM USER_OBJECTS GROUP BY object_type;

Using the Super-View DICTIONARY

  • The DICTIONARY view allows you to search for all Dictionary tables, akin to a search engine.
  • Example query to find tables related to indexes:
  SELECT * FROM DICT WHERE table_name LIKE '%IND%';

Viewing the Dictionary with Application Express

  • Use the Object Browser feature for a user-friendly way to explore Dictionary information:
    • Navigate to SQL Workshop -> Object Browser -> Browse.
  • Note that the Object Browser has limitations:
    • Only objects owned by the user are displayed.
    • Limited information about each object is shown.
    • Not all possible object types are available.

Key Terminology

  • Data Dictionary: Metadata storage of database objects.
  • USER_* tables: Views of objects owned by current user.
  • ALL_* tables: Views of objects user has privileges to access.
  • DBA_* tables: Views of all objects in the database, typically limited to DBAs.

Summary

In this lesson, students have learned to:

  • Describe the purposes of the Data Dictionary.
  • Differentiate between the three types of Data Dictionary views.
  • Write SQL SELECT statements for the Data Dictionary.
  • Use DICTIONARY as a search engine to locate Dictionary tables.