Data Dictionary and Dynamic Performance Views
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
| Prefix | Shows | Who can use it |
|---|---|---|
USER_ | Objects you own | Everyone |
ALL_ | Objects you can access | Everyone |
DBA_ | Everything in this database | DBAs |
CDB_ | Everything in all PDBs | DBAs 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
| View | What it tells you |
|---|---|
DBA_USERS | All users, status, default tablespace |
DBA_TABLES, DBA_INDEXES | Tables and indexes |
DBA_SEGMENTS | Space used by each object |
DBA_DATA_FILES | Datafiles and sizes |
DBA_TABLESPACES | Tablespaces |
DBA_TAB_PRIVS, DBA_SYS_PRIVS | Granted privileges |
DBA_OBJECTS | Every 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.
| View | What it tells you |
|---|---|
V$INSTANCE, V$DATABASE | Basic info |
V$SESSION | Connected sessions |
V$SQL | SQL statements in the shared pool |
V$PARAMETER | Parameters |
V$DATAFILE, V$LOGFILE | Files |
V$LOG, V$ARCHIVED_LOG | Redo logs |
V$SYSTEM_EVENT | Wait 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.

