SQL Guide Chapter

Lesson 12 – Introduction to Data Dictionary Views (or: the database’s own gossip column)

And look, your schema knows things about itself that you have absolutely no idea about: who created which table, what constraints lurk on which columns, and why that one INSERT keeps failing. The data dictionary is the...

oracle 19c mysql notes study chapter

And look, your schema knows things about itself that you have absolutely no idea about: who created which table, what constraints lurk on which columns, and why that one INSERT keeps failing. The data dictionary is the part of Oracle that keeps receipts on all of it.

This lesson is about using those built‑in views to let the database tell you about… itself.

You will learn to:

1. What Is the Data Dictionary?

The database stores two broad kinds of information:

The metadata lives in the data dictionary.

Internally, the dictionary consists of base tables owned by Oracle. You don’t query those directly; instead you query views that sit on top of them.

These views fall into families based on scope:

So you have a kind of metadata onion, going from “just my stuff” to “everyone’s stuff” to “how the whole database is feeling right now.”

2. DICTIONARY / DICT – The Directory of the Dictionary

If you’ve ever wondered “what dictionary views even exist?”, Oracle gives you a meta‑view of the metadata: DICTIONARY (also known as DICT).

SELECT table_name,
           comments
    FROM   dictionary;

You’ll see entries like:

It’s the help index for every other metadata view.

3. USER_OBJECTS, ALL_OBJECTS – What Objects Exist?

3.1 Objects you own: USER_OBJECTS

USER_OBJECTS shows everything you own in your schema: tables, indexes, sequences, views, etc.

SELECT object_name,
           object_type,
           status,
           created,
           last_ddl_time
    FROM   user_objects;

Example check:

SELECT COUNT(*)
    FROM   user_objects;

Tells you how many objects you personally own.

3.2 Objects you can see: ALL_OBJECTS

ALL_OBJECTS shows objects you can access, not just those you own:

SELECT owner,
           object_name,
           object_type
    FROM   all_objects;

You’ll see rows from schemas like SYS, SYSTEM, and application schemas. This includes:

With ALL_OBJECTS you can answer questions like “which user owns table X?” and “do I have access to that package?”

4. USER_TABLES and ALL_TABLES – Table‑Level Info

USER_TABLES describes every table you own:

SELECT table_name,
           num_rows,
           blocks,
           temporary,
           partitioned
    FROM   user_tables;

ALL_TABLES is the broader version, for all accessible tables:

SELECT owner,
           table_name
    FROM   all_tables;

Remember: Oracle stores object and column names in uppercase by default, so query using uppercase identifiers (e.g., 'EMPLOYEES').

5. USER_TAB_COLUMNS – Column‑Level Info

To see columns and their properties, use USER_TAB_COLUMNS (or ALL_TAB_COLUMNS):

SELECT table_name,
           column_name,
           data_type,
           data_length,
           data_precision,
           data_scale,
           nullable,
           data_default
    FROM   user_tab_columns
    WHERE  table_name = 'EMPLOYEES'
    ORDER  BY column_id;

This tells you for each column:

It’s your “what does this table actually look like?” view.

6. USER_CONSTRAINTS and USER_CONS_COLUMNS – Constraint Info

Constraints are described in two main views:

6.1 USER_CONSTRAINTS

SELECT constraint_name,
           constraint_type,
           table_name,
           status
    FROM   user_constraints
    WHERE  table_name = 'EMPLOYEES';

CONSTRAINT_TYPE codes:

This tells you what constraints exist and on which tables.

6.2 USER_CONS_COLUMNS

SELECT constraint_name,
           table_name,
           column_name,
           position
    FROM   user_cons_columns
    WHERE  table_name = 'EMPLOYEES';

This tells you which columns are involved in each constraint.

6.3 Joining the two

To see a combined view of constraints and their columns:

SELECT c.constraint_name,
           c.constraint_type,
           c.table_name,
           cc.column_name,
           c.status
    FROM   user_constraints   c
    JOIN   user_cons_columns cc
           ON c.constraint_name = cc.constraint_name
    WHERE  c.table_name = 'EMPLOYEES'
    ORDER  BY c.constraint_name, cc.position;

Now you can answer questions like:

7. Table and Column Comments – In‑Schema Documentation

You can attach comments to tables and columns to document their purpose.

7.1 Adding comments

-- Table comment
    COMMENT ON TABLE dept IS 'Demo department table';
    
    -- Column comment
    COMMENT ON COLUMN dept.id IS 'Primary key for DEPT';

Once set, comments are stored in the dictionary and follow the object around.

7.2 Querying comments

Use:

Examples:

SELECT table_name,
           comments
    FROM   user_tab_comments
    WHERE  table_name = 'DEPT';
    
    SELECT table_name,
           column_name,
           comments
    FROM   user_col_comments
    WHERE  table_name = 'DEPT';

This is how GUI tools like SQL Developer show “description” text for tables and columns.

8. Summary of Key Dictionary Views

Views you should now recognize and know when to use:

9. What You Should Now Be Able to Do

By the end of this lesson, you should be able to:

You now know how to make the database tell you what’s really going on under the hood—which, in practice, is half of “debugging SQL” and roughly 90% of “finding out who to blame for that constraint you didn’t know existed.”

← previous view source markdown next →
// sql-guide generated from markdown notes