Data Dictionary

The data dictionary is a collection of read-only views that describe the database itself: every table, column, index, constraint, view, procedure, user, privilege, and runtime metric. It's metadata, queryable as SQL.

If you've ever wondered "what columns does this table have?", "who has permission to read that view?", or "which queries are running right now?" — the answer is always a query against a dictionary view. Knowing the dictionary is what separates "uses Oracle" from "owns Oracle".

The Three View Families

Every static dictionary view comes in three flavours, named with a consistent prefix:

For nearly every view, all three exist. For example:

USER_TABLES   — my tables
ALL_TABLES    — tables I can SELECT from
DBA_TABLES    — every table (DBA only)

Choose by scope. As a developer, USER_ is usually what you want — it's namespace-scoped to you and excludes noise. ALL_ widens the view when you need to inspect schemas you've been granted access to. DBA_ is what DBAs use.

The Most Useful Views (by Category)

Tables and Columns

-- List all your tables:
SELECT table_name, num_rows, last_analyzed
FROM   user_tables
ORDER BY table_name;

-- List columns of a specific table:
SELECT column_name, data_type, data_length, nullable, data_default
FROM   user_tab_columns
WHERE  table_name = 'EMPLOYEES'
ORDER BY column_id;

USER_TAB_COLUMNS is the canonical "describe a table" view. column_id is the column order; nullable = 'Y' means the column allows NULL.

-- See per-column statistics (great for understanding selectivity):
SELECT column_name, num_distinct, num_nulls, density, low_value, high_value
FROM   user_tab_col_statistics
WHERE  table_name = 'EMPLOYEES';

Indexes

-- All indexes on a table:
SELECT index_name, index_type, uniqueness, status
FROM   user_indexes
WHERE  table_name = 'EMPLOYEES';

-- Which columns each index covers, in order:
SELECT index_name, column_name, column_position, descend
FROM   user_ind_columns
WHERE  table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

column_position tells you which is the leading column — critical for understanding whether an index helps a query. descend = 'DESC' indicates a descending index.

Constraints

-- All constraints on a table:
SELECT constraint_name, constraint_type, status, validated
FROM   user_constraints
WHERE  table_name = 'EMPLOYEES';

constraint_type:

-- Which columns participate in each constraint:
SELECT constraint_name, column_name, position
FROM   user_cons_columns
WHERE  table_name = 'EMPLOYEES'
ORDER BY constraint_name, position;

-- For foreign keys, what do they reference?
SELECT c.constraint_name, c.table_name, cc.column_name,
       r.table_name AS references_table, rcc.column_name AS references_column
FROM   user_constraints c
JOIN   user_cons_columns cc ON c.constraint_name = cc.constraint_name
JOIN   user_constraints r ON c.r_constraint_name = r.constraint_name
JOIN   user_cons_columns rcc ON r.constraint_name = rcc.constraint_name
                              AND cc.position = rcc.position
WHERE  c.constraint_type = 'R'
  AND  c.table_name = 'EMPLOYEES';

Views

-- All your views and their definitions:
SELECT view_name, text
FROM   user_views
WHERE  view_name = 'EMP_SUMMARY';

The text column contains the view's full SQL definition. (Note: it's a LONG column, which makes it awkward to display — wrap in SUBSTR(text, 1, 4000) or use DBMS_METADATA.GET_DDL to retrieve the definition as a CLOB.)

Sequences

SELECT sequence_name, min_value, max_value, increment_by, cache_size, last_number
FROM   user_sequences
WHERE  sequence_name = 'EMP_SEQ';

last_number is the next value to be issued after the cache, not the most recently issued value.

Synonyms

SELECT synonym_name, table_owner, table_name, db_link
FROM   user_synonyms
ORDER BY synonym_name;

The columns table_owner and table_name describe the target of the synonym — the target can be a view, sequence, or another synonym, but the column names are historical.

Triggers

SELECT trigger_name, trigger_type, triggering_event,
       table_name, status, trigger_body
FROM   user_triggers
WHERE  table_name = 'EMPLOYEES';

status = 'ENABLED' or 'DISABLED'. trigger_type shows when it fires (BEFORE/AFTER/INSTEAD OF + STATEMENT/EACH ROW).

Procedures, Functions, Packages

-- List all your PL/SQL objects:
SELECT object_name, object_type, status, last_ddl_time
FROM   user_objects
WHERE  object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

-- View the source code of a stored unit:
SELECT line, text
FROM   user_source
WHERE  name = 'CALCULATE_BONUS'
  AND  type = 'PROCEDURE'
ORDER BY line;

status = 'INVALID' is your alert that something's broken — usually because a dependency (table, view, other procedure) changed. Recompile with ALTER PROCEDURE x COMPILE.

Users and Privileges

-- Who am I?
SELECT user FROM dual;

-- What roles do I have?
SELECT * FROM user_role_privs;

-- What system privileges?
SELECT * FROM user_sys_privs;

-- What object privileges have I been granted?
SELECT * FROM user_tab_privs_recd;

-- What object privileges have I granted to others?
SELECT * FROM user_tab_privs_made;

For the DBA equivalents covering every user: DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS.

Dynamic Performance Views — V$

While USER_ / ALL_ / DBA_ describe the database structure (mostly static), the V$ views describe its runtime state — currently running sessions, recent SQL, memory usage, wait events, locks. They change second by second.

-- Who's connected right now?
SELECT sid, serial#, username, machine, program, status
FROM   v$session
WHERE  username IS NOT NULL
ORDER BY username;

-- What is each session waiting on?
SELECT s.sid, s.username, w.event, w.seconds_in_wait
FROM   v$session s
LEFT   JOIN v$session_wait w ON s.sid = w.sid
WHERE  s.status = 'ACTIVE';

-- Most expensive recent SQL by elapsed time:
SELECT sql_id, executions, elapsed_time/1e6 AS seconds, sql_text
FROM   v$sql
WHERE  parsing_schema_name = USER
ORDER  BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

V$ views require SELECT ANY DICTIONARY privilege (or SELECT_CATALOG_ROLE). Many DBAs grant SELECT_CATALOG_ROLE to developers for diagnostics.

V$ vs GV$

In RAC (Real Application Clusters), each instance has its own session state. V$ views show only the local instance; GV$ (global view) shows all instances:

-- Sessions on this instance only:
SELECT sid, username FROM v$session;

-- Sessions across the whole cluster:
SELECT inst_id, sid, username FROM gv$session;

For non-RAC databases, V$ and GV$ are equivalent.

DICT and DICTIONARY — Finding the Right View

There are hundreds of dictionary views. To find the right one:

-- Search by name pattern:
SELECT table_name, comments
FROM   dictionary
WHERE  table_name LIKE '%CONSTRAINT%';

-- DICT is a shorter synonym for DICTIONARY:
SELECT * FROM dict WHERE table_name LIKE 'USER_TAB%';

DICTIONARY lists every dictionary view with a one-line description. This is the discoverable index.

-- Find which columns are available in a specific view:
SELECT column_name
FROM   user_tab_columns
WHERE  table_name = 'USER_TABLES'
ORDER BY column_id;

Practical Queries Every Developer Should Know

Find Tables Without a Primary Key

SELECT t.table_name
FROM   user_tables t
LEFT   JOIN user_constraints c
       ON c.table_name = t.table_name AND c.constraint_type = 'P'
WHERE  c.constraint_name IS NULL;

Find All Foreign Keys Pointing at a Given Table

SELECT c.table_name AS child_table,
       cc.column_name AS child_column,
       c.constraint_name
FROM   user_constraints c
JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
JOIN   user_constraints r ON r.constraint_name = c.r_constraint_name
WHERE  c.constraint_type = 'R'
  AND  r.table_name = 'DEPARTMENTS';

Useful when you're about to drop or alter a referenced table.

Find All Indexes That Include a Specific Column

SELECT ic.index_name, ic.column_position, i.uniqueness
FROM   user_ind_columns ic
JOIN   user_indexes i ON i.index_name = ic.index_name
WHERE  ic.column_name = 'DEPARTMENT_ID'
ORDER  BY ic.index_name, ic.column_position;

Before adding a new index, check what already exists.

Find Invalid Objects (Compile Errors)

SELECT object_name, object_type, status, last_ddl_time
FROM   user_objects
WHERE  status = 'INVALID';

After major DDL changes, this is your first sanity check.

Find Recently Modified Objects

SELECT object_name, object_type, last_ddl_time
FROM   user_objects
WHERE  last_ddl_time > SYSDATE - 1
ORDER  BY last_ddl_time DESC;

Useful for "what did I change today?" investigations.

Find the Largest Tables by Row Count

SELECT table_name, num_rows, blocks, last_analyzed
FROM   user_tables
WHERE  num_rows IS NOT NULL
ORDER BY num_rows DESC
FETCH FIRST 10 ROWS ONLY;

num_rows reflects the last statistics gathering, not the live count. For an exact current count, run SELECT COUNT(*).

Find Tables Holding the Most Disk Space

SELECT s.segment_name, s.segment_type,
       ROUND(SUM(s.bytes)/1024/1024) AS mb
FROM   user_segments s
WHERE  s.segment_type IN ('TABLE', 'TABLE PARTITION')
GROUP  BY s.segment_name, s.segment_type
ORDER  BY mb DESC FETCH FIRST 10 ROWS ONLY;

USER_SEGMENTS is the source-of-truth for actual disk usage per object.

Audit Trail of Schema Changes

-- Last 20 objects changed in your schema:
SELECT object_name, object_type, status, created, last_ddl_time
FROM   user_objects
ORDER  BY last_ddl_time DESC
FETCH FIRST 20 ROWS ONLY;

Tablespaces and Free Space (DBA-level)

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024) AS total_mb,
       (SELECT ROUND(SUM(bytes)/1024/1024) FROM dba_free_space f
        WHERE f.tablespace_name = ds.tablespace_name) AS free_mb
FROM   dba_data_files ds
GROUP  BY tablespace_name;

Used to anticipate "tablespace full" errors before they happen.

DBMS_METADATA — Get the DDL

Sometimes you don't just want metadata; you want the actual CREATE statement that would recreate an object.

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM dual;

This returns the full CREATE TABLE including columns, constraints, indexes, storage clauses, and partitioning. The output is exactly what you'd @ from a SQL file.

Other types: 'VIEW', 'INDEX', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'USER'.

-- Get DDL for an object in another schema:
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM dual;

This is how export utilities reconstruct objects.

NLS Parameters

Internationalisation settings live in dictionary views too:

-- Session-level NLS settings:
SELECT * FROM nls_session_parameters;

-- Database-level defaults:
SELECT * FROM nls_database_parameters;

-- Instance-level:
SELECT * FROM nls_instance_parameters;

Critical for understanding why a date format or numeric separator looks unexpected.

Worked Example — Schema Documentation Generator

Build a one-shot query that produces a readable "schema report" for any of your tables:

WITH cols AS (
  SELECT table_name,
         LISTAGG(column_name || ' ' || data_type ||
                 CASE WHEN nullable = 'N' THEN ' NOT NULL' END, ', ')
                 WITHIN GROUP (ORDER BY column_id) AS column_list
  FROM   user_tab_columns
  GROUP  BY table_name
),
pk AS (
  SELECT c.table_name,
         LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS pk_cols
  FROM   user_constraints c
  JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
  WHERE  c.constraint_type = 'P'
  GROUP  BY c.table_name
),
fks AS (
  SELECT c.table_name,
         COUNT(*) AS fk_count
  FROM   user_constraints c
  WHERE  c.constraint_type = 'R'
  GROUP  BY c.table_name
),
idx AS (
  SELECT table_name, COUNT(*) AS index_count
  FROM   user_indexes
  GROUP  BY table_name
)
SELECT t.table_name,
       t.num_rows,
       pk.pk_cols,
       NVL(fks.fk_count, 0) AS foreign_keys,
       NVL(idx.index_count, 0) AS indexes,
       cols.column_list
FROM   user_tables t
LEFT   JOIN cols ON cols.table_name = t.table_name
LEFT   JOIN pk   ON pk.table_name   = t.table_name
LEFT   JOIN fks  ON fks.table_name  = t.table_name
LEFT   JOIN idx  ON idx.table_name  = t.table_name
ORDER  BY t.table_name;

One query, structured schema report. Run nightly into an audit table to track schema evolution.

Common Errors

Interview Corner

SELECT  s.sid, s.username, s.event, s.seconds_in_wait, s.blocking_session,
        b.username AS blocker_user, b.sql_id AS blocker_sql_id
FROM    v$session s
LEFT    JOIN v$session b ON b.sid = s.blocking_session
WHERE   s.blocking_session IS NOT NULL;

SELECT sql_text
FROM   v$sql
WHERE  sql_id = :blocker_sql_id;

SELECT lo.session_id, lo.locked_mode, o.object_name, o.object_type
FROM   v$locked_object lo
JOIN   user_objects o ON o.object_id = lo.object_id
WHERE  lo.session_id = :blocker_sid;

SELECT owner, object_type, status
FROM   all_objects
WHERE  object_name = 'EMPLOYEES';

SELECT grantor, table_name, privilege
FROM   user_tab_privs_recd
WHERE  table_name = 'EMPLOYEES';

GRANT SELECT ON hr.employees TO your_user;   -- direct, not via role

SELECT * FROM all_synonyms WHERE synonym_name = 'EMPLOYEES';

SELECT object_name, original_name, droptime FROM recyclebin;

Related Topics

• DDL Commands — every CREATE/ALTER/DROP updates the dictionary
• Constraints — USER_CONSTRAINTS and USER_CONS_COLUMNS describe every constraint
• Indexes — USER_INDEXES and USER_IND_COLUMNS for index analysis
• Performance — V$SQL, V$SQL_PLAN, V$SESSION_WAIT for live diagnostics
• Transactions & Locks — V$LOCK and V$LOCKED_OBJECT for lock investigation