Data Dictionary and Dynamic Performance Views

Oracle DBA Certification 13 min min read Updated: Nov 16, 2026 Beginner
Data Dictionary and Dynamic Performance Views
Beginner Topic 5 of 6

The Database Describes Itself

Oracle stores information about everything inside the database in special read-only tables called the data dictionary. You never touch those tables directly. You read them through views.

The Four Prefixes

PrefixShowsWho can use it
USER_Objects you ownEveryone
ALL_Objects you can accessEveryone
DBA_Everything in this databaseDBAs
CDB_Everything in all PDBsDBAs in the root
sql SELECT table_name FROM user_tables; SELECT owner, table_name FROM all_tables WHERE owner = 'SHOP'; SELECT owner, COUNT(*) FROM dba_tables GROUP BY owner ORDER BY 2 DESC;

Useful DBA_ Views

ViewWhat it tells you
DBA_USERSAll users, status, default tablespace
DBA_TABLES, DBA_INDEXESTables and indexes
DBA_SEGMENTSSpace used by each object
DBA_DATA_FILESDatafiles and sizes
DBA_TABLESPACESTablespaces
DBA_TAB_PRIVS, DBA_SYS_PRIVSGranted privileges
DBA_OBJECTSEvery object, with status VALID or INVALID
sql -- 10 biggest segments SELECT owner, segment_name, segment_type, ROUND(bytes/1024/1024) AS mb FROM dba_segments ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY; -- invalid objects SELECT owner, object_name, object_type FROM dba_objects WHERE status = 'INVALID';

Dynamic Performance Views (V$)

V$ views show what is happening right now inside the instance. They read memory structures and are rebuilt at every startup.

ViewWhat it tells you
V$INSTANCE, V$DATABASEBasic info
V$SESSIONConnected sessions
V$SQLSQL statements in the shared pool
V$PARAMETERParameters
V$DATAFILE, V$LOGFILEFiles
V$LOG, V$ARCHIVED_LOGRedo logs
V$SYSTEM_EVENTWait events
sql SELECT username, status, COUNT(*) FROM v$session WHERE type = 'USER' GROUP BY username, status;
GV$ views: In RAC (several instances) use GV$ views. They add an INST_ID column so you see every instance.

Finding the Right View

sql SELECT table_name, comments FROM dictionary WHERE table_name LIKE '%TABLESPACE%';
Tip: You do not have to memorize them. Query DICTIONARY and DICT_COLUMNS, or search the Oracle Database Reference.

Conclusion

DBA_ views describe stored objects and V$ views show live activity. Almost every DBA task starts with a query against one of them.

Quick Summary: USER_ = mine, ALL_ = I can see, DBA_ = everything, V$ = live instance.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators