Oracle Data Dictionary Views and Metadata

Perhaps you've heard the term metadata^[1]. Metadata is literally beyond data — in an Oracle database, it's the description of your data, rather than the data itself. Metadata is what lets you answer questions like:

1. What are the names of my tables and columns?
2. What is the maximum size of this table?
3. Who can view this table?
4. When was this user created?

None of these questions are about the rows sitting inside your tables — they're about the structure and permissions surrounding those rows, and that's exactly what metadata captures.

Oracle stores metadata in its own internal set of relational tables. You can't see or modify these tables directly — instead, Oracle exposes a set of data dictionary views that give you read access to the same information through ordinary SQL. Data dictionary views behave exactly like any other view: rows and columns, queryable with a standard SELECT. The difference is entirely in what they describe — not your application data, but the database itself.

The name of a data dictionary view is deliberately informative. Almost every one falls into one of three groups, based on its first word, and once you know the pattern, you can often guess what a view contains before ever looking it up.

That three-way split holds across almost the entire data dictionary, not just for indexes, objects, and extents. Take views on the database's own VIEWS metadata as another example: USER_VIEWS shows views you own, ALL_VIEWS shows every view you can access, and DBA_VIEWS shows every view in the database, visible only to a user holding the DBA role. Once you recognize the pattern — USER_* is mine, ALL_* is mine plus what I can reach, DBA_* is everything — the hundreds of data dictionary views stop looking like hundreds of things to memorize and start looking like a handful of concepts applied consistently.

Oracle's data dictionary tracks, for every object in the database:

1. the object itself,
2. who owns it,
3. its definition, and
4. the privileges granted on it.

For objects that need their own physical storage, Oracle allocates space within a tablespace. Tablespaces track that space usage in one of two ways: dictionary managed, where space allocation is recorded directly in the data dictionary tables, or locally managed, where Oracle instead maintains a bitmap inside each datafile of the tablespace itself. In practice, locally managed has been the default and the near-universal standard since Oracle 9i — you're unlikely to encounter a genuinely dictionary-managed tablespace outside of a very old, unmigrated database. Locally managed tablespaces track only quotas in the data dictionary itself, which dramatically reduces contention on the dictionary tables compared to the older approach — one of the reasons the shift became universal in the first place.

One practical rule worth internalizing early: internally, Oracle stores every column and table name in uppercase, and expects to work with them that way. When you write an alias while creating a view, leave it unquoted. Wrapping an alias in double quotation marks forces Oracle to store that column name in mixed case exactly as typed — which means every future query against that view has to quote the column name the same way just to find it, since Oracle will otherwise look for the uppercase version and come up empty. The simplest rule: never use double quotation marks when creating aliases for a view.

Oracle ships hundreds of data dictionary views. The table below covers a representative handful worth knowing by name:

Data dictionary view name	Description
ALL_CATALOG	All tables, views, and synonyms the current user is allowed to access
ALL_USERS	Names of all Oracle users. Passwords are never displayed.
DBA_CONSTRAINTS	All constraints in the database
DBA_ROLES	All roles that exist in the database
USER_CATALOG	Tables, sequences, views, and other objects owned by the current user
USER_ROLE_PRIVS	Roles assigned to the current user
USER_TAB_COL_STATISTICS	Low, high, and average values in a table's columns
USER_TAB_PRIVS	Privileges granted to the current user
USER_TAB_PRIVS_MADE	Privileges the current user has granted to others
USER_TABLES	Tables owned by the current user, including statistics such as row count and space allocated/used
USER_TYPES	Object types created by the current user

You can use the DESC command within SQL*Plus to display the columns of any table or view — including every data dictionary view listed above. In the next lesson, you'll put that to use looking directly at database tables with SQL*Plus.

It's one thing to know a view's name; it's another to recognize which view actually answers a real-world question. Here are five common questions, each matched to the view that answers it:

1. ALL_CATALOG — What tables can I access that were created by other Oracle users?
2. DBA_ROLES — How many roles were created in the entire database?
3. USER_ROLE_PRIVS — What roles do I have assigned to me?
4. USER_TAB_COL_STATISTICS — In my DAILY_SALES table, what is the average value in the column called TOTAL_SALES?
5. USER_TAB_PRIVS_MADE — Did I grant the SELECT privilege to the MANAGER role?

Notice the pattern holding again here: any question about "everyone" or "the whole database" points to a DBA_* or ALL_* view, while any question about "my own" objects, roles, or privileges points to a USER_* view.

Separate from the data dictionary views themselves, Oracle also maintains a data dictionary cache — an in-memory structure holding frequently needed pieces of the dictionary so the database doesn't have to re-read them from disk constantly. It typically holds:

1. usernames,
2. segment information,
3. profile data,
4. tablespace information, and
5. sequence numbers.

The dictionary cache also stores descriptive metadata about schema objects, which Oracle relies on heavily while parsing SQL cursors and compiling PL/SQL programs — every time Oracle needs to know a table's columns or a procedure's signature during parsing, this cache is very often where that lookup actually resolves.

[1] Metadata: Information (data) about database structures. For example, the metadata about a table includes its name, its column names, and the tablespace in which it resides.