ExamFinch.com My Study

Study guide

15.2 Querying Schema & Object Metadata

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

15.2 Querying Schema & Object Metadata

Key Takeaways Explain This Topic AI

USER_OBJECTS catalogs all schema objects (tables, views, indexes, sequences, packages) and reports their compilation STATUS (VALID or INVALID) and lifecycle timestamps (CREATED, LAST_DDL_TIME).

USER_TABLES provides table properties (TABLESPACE_NAME, STATUS, LOGGING) and optimizer statistics (NUM_ROWS, BLOCKS, AVG_ROW_LEN) which only reflect row counts as of the most recent DBMS_STATS execution.

USER_TAB_COLUMNS contains column-level definitions (DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT), with unquoted identifiers stored in UPPERCASE.

USER_VIEWS stores the defining query expression in the TEXT column (datatype LONG), while USER_SEQUENCES, USER_INDEXES, USER_IND_COLUMNS, and USER_SYNONYMS provide granular visibility into database generators and indexing.

In USER_IND_COLUMNS, the COLUMN_POSITION column indicates the 1-based ordinal position of columns within composite (multi-column) indexes.

Last updated: August 2026

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.

Column Name Datatype Description & Exam Importance

TABLE_NAME VARCHAR2(128) Name of the table (UPPERCASE).

TABLESPACE_NAME VARCHAR2(30) Physical tablespace where table data blocks are stored.

STATUS VARCHAR2(8) VALID (available for DML/queries) or UNUSABLE .

NUM_ROWS NUMBER Approximate number of rows computed during the last optimizer statistics gather.

BLOCKS NUMBER Number of formatted data blocks allocated to the table below high-water mark.

AVG_ROW_LEN NUMBER Average row length in bytes.

LAST_ANALYZED DATE Timestamp when optimizer statistics were last collected via DBMS_STATS .

LOGGING VARCHAR2(3) YES if changes are written to redo log; NO for nologging tables.

READ_ONLY VARCHAR2(3) YES if table is in read-only mode ( ALTER TABLE t READ ONLY; ), else NO .

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

  • Metadata Requirement Primary View Key Columns to Query
  • Object validation & timestamps USER_OBJECTS OBJECT_NAME , OBJECT_TYPE , STATUS , LAST_DDL_TIME
  • Tablespace & optimizer stats USER_TABLES TABLE_NAME , TABLESPACE_NAME , NUM_ROWS , LAST_ANALYZED
  • Column types, lengths, nullability USER_TAB_COLUMNS COLUMN_NAME , DATA_TYPE , DATA_LENGTH , NULLABLE
  • View query definitions USER_VIEWS VIEW_NAME , TEXT_LENGTH , TEXT
  • Sequence increment & caching USER_SEQUENCES SEQUENCE_NAME , INCREMENT_BY , CACHE_SIZE , LAST_NUMBER
  • Index structure & column order USER_IND_COLUMNS INDEX_NAME , COLUMN_NAME , COLUMN_POSITION
  • Synonyms and target objects USER_SYNONYMS SYNONYM_NAME , TABLE_OWNER , TABLE_NAME , DB_LINK