15.2 Querying Schema & Object Metadata

15.2 Querying Schema & Object Metadata

When designing, troubleshooting, or automating Oracle database applications, developers frequently need to inspect database schema objects programmatically. Rather than relying solely on graphical tools, SQL developers must master the core metadata views provided in the data dictionary.

This section covers the structural details, column mechanics, and query patterns for the primary object metadata views tested on the Oracle Database SQL (1Z0-071) exam: USER_OBJECTS, USER_TABLES, USER_TAB_COLUMNS, USER_VIEWS, USER_SEQUENCES, USER_INDEXES, USER_IND_COLUMNS, and USER_SYNONYMS.

---

1. High-Level Catalog Metadata: USER_OBJECTS

The USER_OBJECTS view is the highest-level object catalog for the current user schema. It lists every schema object—including tables, views, indexes, sequences, synonyms, triggers, packages, procedures, and functions.

+-------------------------------------------------------------------------+
|                        KEY USER_OBJECTS COLUMNS                         |
+-------------------------------------------------------------------------+
| OBJECT_NAME     | Name of the object (stored in UPPERCASE by default).   |
| OBJECT_ID       | Internal database-wide surrogate numeric identifier.    |
| OBJECT_TYPE     | TABLE, VIEW, INDEX, SEQUENCE, SYNONYM, PACKAGE, etc.   |
| CREATED         | DATE timestamp when the object was initially created.  |
| LAST_DDL_TIME   | DATE timestamp of the most recent DDL modification.    |
| STATUS          | VALID or INVALID (indicates compilation/readiness).    |
| TEMPORARY       | 'Y' if Global Temporary Table, otherwise 'N'.          |
| GENERATED       | 'Y' if system-generated name (e.g., SYS_C007812).      |
+-------------------------------------------------------------------------+

Checking for Invalid Objects

When underlying tables or views are modified (such as dropping a column or modifying a datatype), dependent views, stored procedures, or triggers become INVALID. Developers query USER_OBJECTS to identify objects requiring recompilation:

-- Identify all invalid objects in the current schema:
SELECT object_name, object_type, last_ddl_time
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type, object_name;

Exam Tip: Tables, sequences, and standard indexes are virtually always VALID. Views, packages, procedures, functions, and triggers can have a STATUS of INVALID if their underlying dependencies are altered or dropped.

---

2. Table-Level Metadata & Statistics: USER_TABLES

The USER_TABLES view (and its shorthand synonym TABS) describes all relational tables owned by the current user.

Critical Distinction: NUM_ROWS vs. COUNT(*)

-- Developer queries optimizer statistics:
SELECT table_name, num_rows, last_analyzed
FROM user_tables
WHERE table_name = 'EMPLOYEES';

Exam Trap: NUM_ROWS in USER_TABLES does NOT reflect the real-time row count! It only displays the number of rows recorded the last time DBMS_STATS.GATHER_TABLE_STATS was executed. If 1,000 rows were inserted ten minutes ago without gathering statistics, NUM_ROWS will show the old count or NULL (if unanalyzed), while SELECT COUNT(*) FROM employees; returns the exact real-time count.

---

3. Column Definitions: USER_TAB_COLUMNS

The USER_TAB_COLUMNS view (and shorthand COLS) provides detailed specifications for every column in the user's tables and views.

+-------------------------------------------------------------------------+
|                      KEY USER_TAB_COLUMNS COLUMNS                       |
+-------------------------------------------------------------------------+
| TABLE_NAME       | Name of table or view containing the column.         |
| COLUMN_NAME      | Column identifier.                                   |
| COLUMN_ID        | 1-based ordinal position of column as created.       |
| DATA_TYPE        | VARCHAR2, NUMBER, DATE, TIMESTAMP(6), CLOB, etc.     |
| DATA_LENGTH      | Maximum column length in bytes.                      |
| DATA_PRECISION   | Total significant digits for NUMBER (or NULL).       |
| DATA_SCALE       | Digits to the right of decimal for NUMBER (or NULL). |
| NULLABLE         | 'Y' if column permits NULLs, 'N' if NOT NULL.        |
| DATA_DEFAULT     | Text of the default value expression (LONG datatype).|
+-------------------------------------------------------------------------+

Inspecting Table Schema Details

-- Query all column specifications for the EMPLOYEES table:
SELECT column_id, column_name, data_type,
       data_length, data_precision, data_scale, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;

Note on Datatypes: For VARCHAR2(50), DATA_LENGTH is 50, while DATA_PRECISION and DATA_SCALE are NULL. For NUMBER(8,2), DATA_PRECISION is 8 and DATA_SCALE is 2. For unconstrained NUMBER, precision and scale are both NULL.

---

4. View Definitions: USER_VIEWS

USER_VIEWS stores the defining SELECT statement and properties of views created by the user:

SELECT view_name, text_length, text
FROM user_views;

• VIEW_NAME: The identifier of the view.
• TEXT_LENGTH: The length (in characters) of the view definition query.
• TEXT: The actual SQL SELECT statement used to construct the view, stored as a LONG datatype.
• READ_ONLY: Displays 'Y' if the view was declared with the WITH READ ONLY clause, otherwise 'N'.

---

5. Sequence Generators: USER_SEQUENCES

The USER_SEQUENCES view (and shorthand SEQ) details the configuration and state of sequence generators.

+-------------------------------------------------------------------------+
|                       KEY USER_SEQUENCES COLUMNS                        |
+-------------------------------------------------------------------------+
| SEQUENCE_NAME    | Identifier of the sequence.                          |
| MIN_VALUE        | Minimum sequence boundary.                           |
| MAX_VALUE        | Maximum sequence boundary.                           |
| INCREMENT_BY     | Step interval between successive sequence values.    |
| CYCLE_FLAG       | 'Y' if sequence wraps around upon reaching limit.    |
| ORDER_FLAG       | 'Y' if sequence guarantees ordered numbers (RAC).    |
| CACHE_SIZE       | Number of pre-allocated sequence values in memory.   |
| LAST_NUMBER      | Next sequence value to be loaded into the SGA cache. |
+-------------------------------------------------------------------------+

-- Inspect sequence configuration:
SELECT sequence_name, min_value, max_value, increment_by, cache_size, last_number
FROM user_sequences
WHERE sequence_name = 'ORDER_ID_SEQ';

Understanding LAST_NUMBER: When CACHE_SIZE = 20 and the sequence is freshly created starting at 1, LAST_NUMBER displays 21. This indicates the next sequence value that will be fetched from disk into the cache once the current block of 20 cached numbers is consumed.

---

6. Indexes & Index Columns: USER_INDEXES & USER_IND_COLUMNS

Indexes accelerate query performance and enforce unique/primary key constraints. Investigating indexes requires two complementary views:

                         INDEX DICTIONARY RELATIONSHIP
                                      │
          ┌───────────────────────────┴───────────────────────────┐
          │                                                       │
     USER_INDEXES                                          USER_IND_COLUMNS
     (Index-Level Attributes)                              (Column-Level Mappings)
     - INDEX_NAME                                          - INDEX_NAME
     - TABLE_NAME                                          - TABLE_NAME
     - INDEX_TYPE (NORMAL, BITMAP)                         - COLUMN_NAME
     - UNIQUENESS (UNIQUE, NONUNIQUE)                      - COLUMN_POSITION (1, 2, 3...)
     - STATUS (VALID, UNUSABLE)                            - DESCEND (ASC, DESC)

Inspecting Composite Indexes and Column Order

-- Query index columns and their ordinal positioning:
SELECT i.table_name, i.index_name, i.uniqueness, i.status,
       ic.column_position, ic.column_name, ic.descend
FROM user_indexes i
JOIN user_ind_columns ic 
  ON i.index_name = ic.index_name
WHERE i.table_name = 'ORDERS'
ORDER BY i.index_name, ic.column_position;

• COLUMN_POSITION: A 1-based integer indicating the exact left-to-right order of the column within a composite (multi-column) index. This order is critical because leading columns govern index usability in WHERE clauses.

---

7. Aliases & Synonyms: USER_SYNONYMS

The USER_SYNONYMS (and shorthand SYN) view lists private synonyms owned by the current user:

SELECT synonym_name, table_owner, table_name, db_link
FROM user_synonyms;

• SYNONYM_NAME: The local alias created using CREATE SYNONYM.
• TABLE_OWNER: The schema that owns the target underlying object.
• TABLE_NAME: The name of the underlying target table, view, sequence, or package.
• DB_LINK: The database link name if the synonym references a remote database object (or NULL if local).

---

Summary Matrix of Primary Schema Metadata Views