Oracle SQL Data Dictionary Views: USER_, ALL_ and DBA_

Oracle SQL Data Dictionary Views: Complete Guide

The data dictionary is Oracle’s internal repository of metadata — information about every object in the database. It contains details about tables, columns, constraints, indexes, views, sequences, and much more. Querying the data dictionary is one of the most practical skills for any Oracle developer or DBA.

Learning Objectives
By the end of this guide you will understand the three main view families (USER_, ALL_, DBA_), know the most important dictionary views, master object naming rules, learn how to use the DUAL table, and explore dynamic performance views.

1. What the Data Dictionary Is

The data dictionary consists of read-only tables and views maintained automatically by Oracle. It stores metadata about all database objects. You query it using regular SELECT statements, but you cannot modify it directly — Oracle updates it whenever you run DDL statements.

-- Example: Query your own tables
SELECT table_name, num_rows
FROM   user_tables
ORDER BY table_name;

Read-only by design: The data dictionary is maintained exclusively by Oracle. Any DDL you run (CREATE, ALTER, DROP) automatically updates the dictionary.

2. The Three View Families — USER_, ALL_, DBA_

Oracle provides three families of data dictionary views. The prefix determines the scope of data you can see.

Prefix	Shows	OWNER column?	Typical use
USER_	Objects owned by the current user	No	Most common for everyday work
ALL_	Objects the current user can access (own + granted)	Yes	When working with objects in other schemas
DBA_	All objects in the entire database	Yes	DBAs and users with high privileges

-- Your own objects only
SELECT table_name FROM user_tables;

-- Your objects plus granted objects in other schemas
SELECT owner, table_name 
FROM   all_tables
WHERE  owner != USER();

-- Everything in the database (requires privileges)
SELECT owner, table_name 
FROM   dba_tables
WHERE  owner = 'HR';

Important difference: USER_ views do not have an OWNER column (everything belongs to you). ALL_ and DBA_ views include the OWNER column.

3. Key Views for Tables and Columns

View	What it shows	Key columns
USER_TABLES	Tables owned by the current user	TABLE_NAME, NUM_ROWS, LAST_ANALYZED
USER_TAB_COLUMNS	Columns in the current user’s tables	TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
USER_OBJECTS	All schema objects	OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED

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

4. Key Views for Constraints

View	What it shows	Key columns
USER_CONSTRAINTS	Constraints on your tables	CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS
USER_CONS_COLUMNS	Columns belonging to each constraint	CONSTRAINT_NAME, COLUMN_NAME, POSITION

Constraint type codes:
P = Primary Key U = Unique R = Foreign Key C = Check (includes NOT NULL)

5. Key Views for Indexes, Views, and Sequences

View	What it shows	Key columns
USER_INDEXES	Indexes on your tables	INDEX_NAME, INDEX_TYPE, UNIQUENESS
USER_VIEWS	Views you own	VIEW_NAME, TEXT
USER_SEQUENCES	Sequences you own	SEQUENCE_NAME, INCREMENT_BY, LAST_NUMBER

6. Object Names Are Stored in UPPERCASE

Oracle stores all unquoted object names in the data dictionary in UPPERCASE.

-- Correct way to query
SELECT column_name 
FROM   user_tab_columns 
WHERE  table_name = 'EMPLOYEES';

-- This returns zero rows
WHERE  table_name = 'employees';

Rule to remember: Always use UPPERCASE when filtering data dictionary views by object names (unless the object was created with double quotes to preserve case).

7. DUAL — The One-Row Utility Table

DUAL is a special one-row, one-column table owned by SYS. It is used when you want to evaluate expressions or call functions without referencing a real table.

SELECT SYSDATE FROM dual;
SELECT USER FROM dual;
SELECT 2 * 3 FROM dual;
SELECT UPPER('hello') FROM dual;

8. Dynamic Performance Views (V$ Views)

In addition to the static data dictionary, Oracle provides dynamic performance views (prefixed with V$). These show real-time information about the running database instance.

View	What it shows
V$SESSION	Currently connected sessions
V$DATABASE	Database-level information
V$SQL	SQL statements in the shared pool

9. Interactive Practice Quiz

Q1. Why does this query return zero rows?
SELECT column_name FROM user_tab_columns WHERE table_name = 'employees';

A. USER_TAB_COLUMNS does not contain column information B. Object names in the dictionary are stored in UPPERCASE C. You must use ALL_TAB_COLUMNS instead D. The table does not exist

Q2. In USER_CONSTRAINTS, you see a constraint with CONSTRAINT_TYPE = 'C' and SEARCH_CONDITION = '"LAST_NAME" IS NOT NULL'. What is this?

A. A manually created CHECK constraint B. A NOT NULL constraint (Oracle stores NOT NULL as type C) C. A PRIMARY KEY constraint D. An error in the dictionary

Q3. Which view shows tables in other schemas that you have been granted access to?

A. USER_TABLES B. ALL_TABLES C. DBA_TABLES D. USER_OBJECTS

Conclusion

The Oracle data dictionary is an incredibly powerful tool for exploring and understanding your database. Mastering the USER_, ALL_, and DBA_ view families, along with the key views for tables, columns, constraints, and indexes, will make you far more effective when working with Oracle SQL.

Practice querying these views regularly — they are essential for troubleshooting, documentation, and day-to-day database work.