List of important Data Dictionary queries in Oracle 19c

 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%';

---