Here's a comprehensive list of important Data Dictionary queries in Oracle 19c and the useful information you can extract from each. These are helpful for DBAs and developers when managing, troubleshooting, or analyzing an Oracle database.
🔷 1. USER_TABLES
Query:
SELECT * FROM USER_TABLES;
Use: Lists all tables owned by the current user.
Info you get:
-
Table name
-
Tablespace name
-
Row count estimate
-
Last analyzed date
-
Compression info
🔷 2. ALL_TABLES / DBA_TABLES
Query:
SELECT * FROM ALL_TABLES; -- Tables accessible to user
SELECT * FROM DBA_TABLES; -- All tables in the DB (DBA only)
Use: Get metadata about all tables in the schema or entire database.
🔷 3. USER_TAB_COLUMNS
Query:
SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES';
Use: List all columns and data types of a specific table.
Info:
-
Column names
-
Data types and lengths
-
Null constraints
-
Default values
🔷 4. ALL_CONSTRAINTS / USER_CONSTRAINTS
Query:
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES';
Use: Get all constraints (PK, FK, Unique, Check) on a table.
Info:
-
Constraint type
-
Status (enabled/disabled)
-
Related table (for FKs)
🔷 5. USER_CONS_COLUMNS
Query:
SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES';
Use: Shows columns involved in constraints.
🔷 6. USER_INDEXES / USER_IND_COLUMNS
Query:
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'EMPLOYEES';
SELECT * FROM USER_IND_COLUMNS WHERE INDEX_NAME = 'EMP_NAME_IDX';
Use: List indexes on a table and the columns used in them.
🔷 7. USER_SEQUENCES
Query:
SELECT * FROM USER_SEQUENCES;
Use: Lists all sequences (used for generating unique values).
🔷 8. USER_VIEWS / DBA_VIEWS
Query:
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
Use: Get view definitions and list of views in your schema.
🔷 9. DBA_TAB_PRIVS / DBA_COL_PRIVS
Query:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'HR';
Use: Find object privileges granted to or by a user.
🔷 10. ROLE_TAB_PRIVS
Query:
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'DBA';
Use: See privileges granted through a role.
🔷 11. DBA_USERS
Query:
SELECT * FROM DBA_USERS;
Use: List all users, their account status, lock status, default tablespaces.
🔷 12. DBA_SYS_PRIVS / DBA_ROLE_PRIVS
Query:
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HR';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'HR';
Use: Shows system privileges and roles granted to users.
🔷 13. V$SESSION
Query:
SELECT SID, SERIAL#, USERNAME, STATUS, OSUSER, MACHINE FROM V$SESSION;
Use: Check current sessions, active users, and their client machine info.
🔷 14. V$PROCESS
Query:
SELECT * FROM V$PROCESS;
Use: View background and user processes connected to Oracle.
🔷 15. DBA_DATA_FILES
Query:
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 AS SIZE_MB FROM DBA_DATA_FILES;
Use: Get details about data files in tablespaces.
🔷 16. DBA_TABLESPACES
Query:
SELECT * FROM DBA_TABLESPACES;
Use: List of all tablespaces, status, type (permanent/temp/undo).
🔷 17. DBA_FREE_SPACE
Query:
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
Use: Shows free space in each tablespace.
🔷 18. DBA_EXTENTS
Query:
SELECT * FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'EMPLOYEES';
Use: Details of extents allocated to objects (storage usage).
🔷 19. V$SGA / V$SGAINFO / V$PGA_TARGET_ADVICE
Use: Memory usage and tuning information.
Examples:
SELECT * FROM V$SGAINFO;
SELECT * FROM V$PGA_TARGET_ADVICE;
🔷 20. DBA_HIST_SQLSTAT / V$SQL
Use: Get SQL performance history and currently executing queries.
Bonus: Data Dictionary Structure Tables
-
DICT / DICTIONARY – list of all data dictionary views.
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE '%USER%';
