1Z0-071 Exam Prep
Chapter 12
Volume 3
The data dictionary is Oracle's internal repository of metadata — information about every object in the database. The 1Z0-071 exam tests the three view families (USER_, ALL_, DBA_), the key views you query to find tables, columns, constraints, and indexes, and how object names are stored in the dictionary.
This topic is often underestimated. It is not difficult — but the exam tests it with specific view names and specific column names, so vague knowledge loses marks.
In this post
- What the data dictionary is
- The three view families — USER_, ALL_, DBA_
- Key views for tables and columns
- Key views for constraints
- Key views for indexes, views, and sequences
- Object names are stored in UPPERCASE
- DUAL — the one-row utility table
- Dynamic performance views — V$ views
- Quick reference
- Practice questions
01 — What the data dictionary is
The data dictionary is a set of read-only tables and views maintained by Oracle. It holds metadata — descriptions of all database objects: tables, columns, constraints, indexes, views, sequences, users, privileges, and more.
You query the data dictionary using regular SELECT statements. You cannot INSERT, UPDATE, or DELETE from data dictionary views — Oracle maintains them automatically whenever you run DDL.
-- Query the data dictionary like any other table SELECT table_name, num_rows FROM user_tables ORDER BY table_name; -- The dictionary is updated automatically when DDL runs CREATE TABLE test_tbl (id NUMBER); -- After this runs, user_tables immediately shows test_tbl DROP TABLE test_tbl; -- After this runs, test_tbl is removed from user_tables
Read-only by design: Attempting to INSERT, UPDATE, or DELETE from a data dictionary view raises an error. The dictionary is maintained exclusively by Oracle — you interact with it only through SELECT and through DDL statements that trigger Oracle to update it internally.
02 — The three view families — USER_, ALL_, DBA_
Every major data dictionary concept has three views — one for each scope of visibility. The prefix determines whose objects you see.
| Prefix | Shows | OWNER column? | Typical user |
|---|---|---|---|
USER_ | Objects owned by the current user | No — owner is always the current user | Any user — most commonly used |
ALL_ | Objects the current user can access (own + granted) | Yes | Any user who has grants on other schemas |
DBA_ | All objects in the entire database | Yes | DBAs and privileged users only |
-- USER_ views: your own objects only SELECT table_name FROM user_tables; -- No OWNER column — everything belongs to you -- ALL_ views: your objects + anything you have access to SELECT owner, table_name FROM all_tables WHERE owner != USER(); -- Shows tables in other schemas you have SELECT privilege on -- DBA_ views: everything in the database SELECT owner, table_name FROM dba_tables WHERE owner = 'HR'; -- Requires DBA privilege or SELECT on dba_ views
KEY DIFFERENCE — USER_ has no OWNER column: Because USER_ views only show your own objects, there is no OWNER column — it would always be the same value. ALL_ and DBA_ views include an OWNER column so you can see who owns each object. The exam tests this distinction directly.
03 — Key views for tables and columns
| View | What it shows | Key columns |
|---|---|---|
USER_TABLES | Tables owned by the current user | TABLE_NAME, NUM_ROWS, LAST_ANALYZED |
USER_TAB_COLUMNS | Columns in the current user's tables | TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, COLUMN_ID |
USER_OBJECTS | All schema objects owned by the user | OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED, LAST_DDL_TIME |
-- List all tables owned by the current user SELECT table_name, num_rows FROM user_tables ORDER BY table_name; -- List all columns for the EMPLOYEES table SELECT column_name, data_type, data_length, nullable FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ORDER BY column_id; -- Find all schema objects and their status SELECT object_name, object_type, status FROM user_objects WHERE object_type = 'VIEW'; -- STATUS = 'VALID' or 'INVALID' -- INVALID views: base table was dropped or altered in a breaking way
USER_OBJECTS covers everything: Unlike the type-specific views (USER_TABLES, USER_VIEWS etc.), USER_OBJECTS shows all object types in one place — tables, views, indexes, sequences, procedures, synonyms. Use OBJECT_TYPE to filter by type.
04 — Key views for constraints
| View | What it shows | Key columns |
|---|---|---|
USER_CONSTRAINTS | Constraints defined on the user's tables | CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS, SEARCH_CONDITION, R_CONSTRAINT_NAME |
USER_CONS_COLUMNS | Columns involved in each constraint | CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION |
-- List all constraints on the EMPLOYEES table SELECT constraint_name, constraint_type, status, search_condition FROM user_constraints WHERE table_name = 'EMPLOYEES'; -- CONSTRAINT_TYPE codes: -- P = PRIMARY KEY -- U = UNIQUE -- R = FOREIGN KEY (Referential integrity) -- C = CHECK (includes NOT NULL) -- Find which columns are in a specific constraint SELECT column_name, position FROM user_cons_columns WHERE constraint_name = 'EMP_PK' ORDER BY position;
NOT NULL is stored as a CHECK constraint: In USER_CONSTRAINTS, NOT NULL constraints appear with CONSTRAINT_TYPE = 'C' (CHECK). The SEARCH_CONDITION column shows something like
"LAST_NAME" IS NOT NULL. The exam tests whether you know that NOT NULL is stored as type 'C', not as a separate type.
05 — Key views for indexes, views, and sequences
| View | What it shows | Key columns |
|---|---|---|
USER_INDEXES | Indexes on the user's tables | INDEX_NAME, TABLE_NAME, INDEX_TYPE, UNIQUENESS, STATUS |
USER_IND_COLUMNS | Columns in each index | INDEX_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION |
USER_VIEWS | Views owned by the user | VIEW_NAME, TEXT (the SELECT statement) |
USER_SEQUENCES | Sequences owned by the user | SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE, LAST_NUMBER |
USER_SYNONYMS | Private synonyms owned by the user | SYNONYM_NAME, TABLE_OWNER, TABLE_NAME |
USER_TAB_COMMENTS | Comments on tables | TABLE_NAME, TABLE_TYPE, COMMENTS |
USER_COL_COMMENTS | Comments on columns | TABLE_NAME, COLUMN_NAME, COMMENTS |
-- Check indexes on the EMPLOYEES table SELECT index_name, index_type, uniqueness FROM user_indexes WHERE table_name = 'EMPLOYEES'; -- See the full definition of a view SELECT view_name, text FROM user_views WHERE view_name = 'EMP_DEPT50'; -- Check sequence current state SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size, last_number FROM user_sequences WHERE sequence_name = 'EMP_SEQ';
06 — Object names are stored in UPPERCASE
This is a small rule with a large impact on exam answers. Oracle stores all object names in the data dictionary in UPPERCASE unless you created them with double quotes to preserve case.
-- Table created with a normal name (stored as EMPLOYEES in the dictionary) CREATE TABLE employees (...); CREATE TABLE EMPLOYEES (...); CREATE TABLE Employees (...); -- All three are stored as EMPLOYEES in user_tables -- WRONG: this returns zero rows SELECT column_name FROM user_tab_columns WHERE table_name = 'employees'; -- Returns nothing — the dictionary stores 'EMPLOYEES', not 'employees' -- CORRECT: use UPPERCASE when querying the dictionary SELECT column_name FROM user_tab_columns WHERE table_name = 'EMPLOYEES'; -- Exception: quoted identifiers preserve case CREATE TABLE "MyTable" (...); -- Stored as 'MyTable' in the dictionary — mixed case preserved -- Must be queried with the exact case and double quotes every time
Always use UPPERCASE in dictionary WHERE clauses: When filtering data dictionary views by object name, the value must be in UPPERCASE.
WHERE table_name = 'employees' returns zero rows. WHERE table_name = 'EMPLOYEES' returns the row. The exam will include lowercase filter values as wrong answer options.
07 — DUAL — the one-row utility table
DUAL is a special single-row, single-column table owned by SYS. It is accessible to all users. It exists so you can SELECT an expression or function result without needing a real table.
-- DUAL has one row and one column called DUMMY with value 'X' SELECT * FROM dual; -- Returns: one row, DUMMY = 'X' -- Common uses SELECT SYSDATE FROM dual; -- current date and time SELECT USER FROM dual; -- current username SELECT emp_seq.NEXTVAL FROM dual; -- next sequence value SELECT 2 * 3 FROM dual; -- arithmetic calculation SELECT UPPER('hello') FROM dual; -- function test
Why DUAL exists: Oracle SQL requires a FROM clause in every SELECT statement. When you just want to evaluate an expression or call a function without querying a real table, DUAL provides the syntactically required FROM clause while contributing exactly one row to the result.
08 — Dynamic performance views — V$ views
In addition to the static data dictionary, Oracle has dynamic performance views — the V$ views — which show real-time information about the running database instance. These are maintained in memory, not on disk.
| View | What it shows |
|---|---|
V$SESSION | Currently connected sessions |
V$INSTANCE | Current database instance information |
V$DATABASE | Database-level information |
V$SQL | SQL statements currently in the shared pool |
V$ views vs data dictionary views: Data dictionary views (USER_, ALL_, DBA_) show structural metadata about objects — what tables exist, what columns they have. V$ views show runtime state — what sessions are connected, what SQL is running. Both are queried with SELECT but they serve different purposes. The 1Z0-071 exam only lightly touches V$ views — knowing they exist and what they represent is enough.
09 — Quick reference
| View | What to use it for |
|---|---|
USER_TABLES | List your tables, row counts |
USER_TAB_COLUMNS | Column names, data types, nullable status |
USER_OBJECTS | All object types, STATUS (VALID/INVALID) |
USER_CONSTRAINTS | Constraint names, types (P/U/R/C), status |
USER_CONS_COLUMNS | Which columns belong to which constraint |
USER_INDEXES | Index names, uniqueness, status |
USER_IND_COLUMNS | Which columns are in each index |
USER_VIEWS | View names and their full SELECT definition |
USER_SEQUENCES | Sequence settings, LAST_NUMBER |
USER_SYNONYMS | Private synonym names and their targets |
| Rule | Detail |
|---|---|
| USER_ — no OWNER column | All objects belong to current user — OWNER would be redundant |
| ALL_ and DBA_ — have OWNER column | Needed to distinguish objects from different schemas |
| Object names in dictionary are UPPERCASE | WHERE table_name = 'EMPLOYEES' — not 'employees' |
| NOT NULL stored as type 'C' | In USER_CONSTRAINTS, NOT NULL shows as CHECK constraint |
| DUAL — one row, one column | Used with SELECT when no real table is needed |
| Data dictionary is read-only | Cannot INSERT/UPDATE/DELETE — maintained by Oracle DDL |
10 — Practice questions
Q1 — A developer runs this query to find columns on the EMPLOYEES table but gets zero rows back:
What is the most likely reason?
SELECT column_name FROM user_tab_columns WHERE table_name = 'employees';What is the most likely reason?
Q2 — A developer queries USER_CONSTRAINTS for the EMPLOYEES table and sees a constraint with CONSTRAINT_TYPE = 'C' and SEARCH_CONDITION = '"LAST_NAME" IS NOT NULL'. What type of constraint is this?
Q3 — Which data dictionary view would a regular (non-DBA) user query to see tables in other schemas that they have been granted SELECT privilege on?
Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
Chapter 12 covers every data dictionary view tested on the exam — with practice sets for the view families, column names, constraint type codes, and object name casing. Instant PDF download.
Get the full guide — $25 →
Chapter 12 covers every data dictionary view tested on the exam — with practice sets for the view families, column names, constraint type codes, and object name casing. Instant PDF download.
No comments:
Post a Comment