What is the Data Dictionary?#

When you create a table, where does Oracle store the fact that the table exists? When you grant a privilege, where is that rule saved?

It is all stored in the Data Dictionary. The Data Dictionary is a collection of read-only tables and views owned by the SYS user. It contains the metadata (data about data) for the entire database instance.

You should never (and generally cannot) modify these tables directly via UPDATE or DELETE. Instead, you interact with them via DDL (CREATE, ALTER, DROP) and read them via SELECT.

The USER, ALL, and DBA Hierarchy#

Most static data dictionary views are grouped into three prefixes based on scope and permissions.

1. USER_TABLES: Shows all tables owned by the schema you logged in as.
2. ALL_TABLES: Shows all tables you own, plus tables owned by other schemas that you have been granted SELECT access to.
3. DBA_TABLES: Shows every table in the database, including system tables. You must have the DBA role or SELECT ANY DICTIONARY privilege to query these.

Essential Static Views#

Here are the most critical views every Oracle developer must know. (We will use the ALL_ prefix, but DBA_ works too).

1. ALL_OBJECTS

The master list. Contains every table, view, procedure, function, trigger, and index you can access.

SELECT object_name, object_type, last_ddl_time, status 
FROM all_objects 
WHERE owner = 'RMS' 
ORDER BY last_ddl_time DESC;

2. ALL_TAB_COLUMNS

Allows you to search for columns across the entire database. Very useful when you know a column name but forgot which table it lives in.

SELECT table_name, data_type, data_length 
FROM all_tab_columns 
WHERE column_name = 'ITEM' AND owner = 'RMS';

3. ALL_INDEXES and ALL_IND_COLUMNS

Use these to verify if a query will be performant, or to reverse-engineer access paths.

SELECT i.index_name, c.column_name, c.column_position
FROM all_indexes i
JOIN all_ind_columns c ON i.index_name = c.index_name
WHERE i.table_name = 'ITEM_MASTER'
ORDER BY i.index_name, c.column_position;

Dynamic Performance Views (V$)#

While DBA_ views show static metadata, V$ views show the live, real-time state of the database memory and processes. They are populated dynamically from memory structures, not from disk.

1. V$SESSION

The most important view for troubleshooting. Shows who is logged in and what they are doing.

SELECT sid, serial#, username, status, machine, sql_id
FROM v$session
WHERE status = 'ACTIVE' AND username IS NOT NULL;

2. V$SQL

Shows the actual SQL statements currently cached in the Shared Pool. You can join V$SESSION.SQL_ID to V$SQL.SQL_ID to see exactly what query a user is executing right now!

SELECT sql_text, executions, elapsed_time/1000000 as seconds
FROM v$sql
WHERE sql_id = '7v9a1b2c3d4e5';

Generating SQL with SQL#

One of the most powerful uses of the Data Dictionary is writing SQL queries that generate other SQL queries.

Imagine you need to drop 50 backup tables that start with TMP_. Instead of writing 50 DROP statements manually, let the dictionary do it:

SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;' 
FROM user_tables 
WHERE table_name LIKE 'TMP_%';

-- Output:
-- DROP TABLE TMP_SALES_2024 CASCADE CONSTRAINTS;
-- DROP TABLE TMP_INVENTORY_BAK CASCADE CONSTRAINTS;

You can copy-paste the output and execute it, or wrap it in a PL/SQL EXECUTE IMMEDIATE block for full automation.

Common Gotchas#

Key Takeaways#