Managing Objects with Data Dictionary Views
Managing Objects with Data Dictionary Views
This domain tests finding metadata through Oracle's data dictionary. You must choose the correct view prefix, join related dictionary views, and interpret constraint and object information.
Core rules
- USER_ views show objects owned by the current user. ALL_ views show objects the current user can access, including owned objects. DBA_ views show all objects and require DBA privileges.
- Static dictionary views include USER_TABLES, USER_TAB_COLUMNS, USER_CONSTRAINTS, USER_CONS_COLUMNS, USER_VIEWS, USER_SEQUENCES, USER_INDEXES, USER_IND_COLUMNS, USER_OBJECTS, and USER_SYNONYMS.
- Constraint types include P for primary key, R for foreign key, U for unique, C for check or not null, and V for check option on a view.
- USER_CONS_COLUMNS links constraint names to their columns and includes POSITION for column order. USER_IND_COLUMNS similarly links indexes to columns.
- Dynamic performance views, commonly named V$, show runtime information and are exposed through V$ views. They are separate from static dictionary views.
- In a multitenant database, CDB_ views report across the container database; USER_, ALL_, and DBA_ views remain scoped to the current container context.
Worked example
SELECT c.constraint_name, c.constraint_type, cc.column_name FROM user_constraints c JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name WHERE c.table_name = 'EMPLOYEES' ORDER BY c.constraint_name, cc.position;
USER_CONSTRAINTS supplies the constraint name and type. USER_CONS_COLUMNS supplies the constrained columns. Ordering by POSITION preserves composite-key column order.
Traps
Object names in dictionary views are usually uppercase unless quoted identifiers were created. USER_ views do not show another user's objects merely because you have object privileges; use ALL_ views for accessible objects. Joining constraints to columns only by constraint name can cross tables if names are not unique in the query context, so include the table or owner where appropriate. ALL_ views include the current user's own objects.