1Z0-149: Managing Objects with Data Dictionary Views

What this domain tests

Data dictionary views describe database objects. The exam tests USER_, ALL_, and DBA_ views and the specific views used to inspect tables, columns, constraints, indexes, views, sequences, and synonyms.

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 privileges such as SELECT_CATALOG_ROLE or DBA.
• USER_TABLES lists tables; USER_TAB_COLUMNS lists columns with data types and nullability.
• USER_CONSTRAINTS and USER_CONS_COLUMNS describe constraints and their columns.
• USER_INDEXES and USER_IND_COLUMNS describe indexes; USER_VIEWS stores view text; USER_SEQUENCES shows sequence settings; USER_SYNONYMS lists private synonyms.
• USER_OBJECTS lists object names and types. DICTIONARY and DICT_COLUMNS describe the dictionary itself.
• Object names are stored in uppercase unless created with quoted identifiers.

Worked example

SELECT column_name, data_type, nullable FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ORDER BY column_id;

USER_TAB_COLUMNS contains one row per column of each table owned by the current user. The table name must match the stored case, which is usually uppercase. COLUMN_ID gives the column's position, so ordering by it reproduces the table's column order.

Traps

• Querying USER_TABLES for another user's table; use ALL_TABLES or DBA_TABLES.
• Comparing table_name = 'employees' when the object was created unquoted and stored as EMPLOYEES.
• Confusing USER_INDEXES with USER_IND_COLUMNS; the latter has the indexed columns.
• Expecting USER_SYNONYMS to include public synonyms; it does not.
• Assuming USER_OBJECTS contains column-level details; use USER_TAB_COLUMNS.