Mastering Oracle Data Dictionary: Types, Views, and Practical Queries

Classification of Oracle Data Dictionary

Oracle data dictionary objects are divided into four categories:

Static data dictionary tables

Static data dictionary views

Dynamic performance tables (created after instance startup)

Dynamic performance views

Static Data Dictionary Tables and Views

Static tables can only be maintained by the Oracle user. Static views are read‑only; users query them to obtain metadata about the database.

Common view name prefixes and their meanings: USER_ – objects owned by the current user ALL_ – objects accessible to the current user DBA_ – all objects in the database (requires DBA role)

Example queries:

SELECT * FROM dba_tables;

SELECT * FROM all_tables;

SELECT * FROM user_tables;

Note: Views starting with DBA_ can only be accessed by the SYS user; other users must prefix the view name with

SYS.

Dynamic Performance Tables and Views

Dynamic performance tables are created in the SYS schema after the instance starts and store runtime performance data. They can be inspected via views such as: SELECT name FROM v_$fixed_table; Access to dynamic performance views is limited to the SYS user or users with the DBA role.

Available at different startup phases:

During NOMOUNT: v$parameter, v$sga, v$session, v$process, v$instance, v$version, v$option During MOUNT: adds v$log, v$logfile, v$datafile, v$controlfile, v$database, v$thread, v$datafile_header After full startup:

v_$fixed_table

Common View Families

Frequently used view families include: COL_PRIVS – column privileges EXTENTS – storage allocation details INDEXES – index metadata OBJECT – object information SEGMENTS – segment (table/index) storage info SEQUENCES – sequence definitions SOURCE – source code of stored procedures, functions, packages SYNONYMS – synonym definitions TABLES – table information TRIGGERS – trigger definitions USERS – user accounts and default tablespaces VIEWS – view definitions

Permission Views

Views that expose granted privileges: ROLE_SYS_PRIVS – system privileges granted to roles ROLE_TAB_PRIVS – object privileges granted to roles USER_ROLE_PRIVS – roles granted to the current user USER_SYS_PRIVS – system privileges granted to the current user USER_TAB_PRIVS_MADS – table object privileges for the user USER_TAB_PRIVS_RECD – received table privileges USER_COL_PRIVS_MADS – column privileges granted to the user USER_COL_PRIVS_RECD – received column privileges

Practical SQL Queries

Below are representative queries for inspecting various dictionary objects.

Users and Roles

SELECT username, default_tablespace FROM user_users;

SELECT * FROM user_role_privs;

SELECT * FROM user_sys_privs;

SELECT * FROM user_tab_privs;

Tables

SELECT * FROM user_tables;

SELECT object_name, object_id FROM user_objects WHERE INSTR(object_name,'LOG')>0;

SELECT object_name, created FROM user_objects WHERE object_name=UPPER('&table_name');

SELECT SUM(bytes)/(1024*1024) AS "size(M)" FROM user_segments WHERE segment_name=UPPER('&table_name');

Indexes

SELECT index_name, index_type, table_name FROM user_indexes ORDER BY table_name;

SELECT * FROM user_ind_columns WHERE index_name=UPPER('&index_name');

Sequences

SELECT * FROM user_sequences;

Views

SELECT view_name FROM user_views;

SELECT text FROM user_views WHERE view_name=UPPER('&view_name');

Synonyms

SELECT * FROM user_synonyms;

Constraints

SELECT constraint_name, constraint_type, search_condition, r_constraint_name FROM user_constraints WHERE table_name = UPPER('&table_name');

SELECT c.constraint_name, c.constraint_type, cc.column_name FROM user_constraints c, user_cons_columns cc WHERE c.owner = UPPER('&table_owner') AND c.table_name = UPPER('&table_name') AND c.owner = cc.owner AND c.constraint_name = cc.constraint_name ORDER BY cc.position;

Functions and Procedures

SELECT object_name, status FROM user_objects WHERE object_type='FUNCTION';

SELECT object_name, status FROM user_objects WHERE object_type='PROCEDURE';

SELECT text FROM all_source WHERE owner = USER AND name = UPPER('&plsql_name');

The article concludes with a comprehensive list of Oracle dictionary objects and corresponding queries, enabling developers and DBAs to explore metadata, monitor performance, and manage database objects effectively.