How to Add Annotations to Oracle Tables

Applications and tools often need more about a column than its name and type: a display label, a unit, an owner, a hint for the user interface. Comments hold one free-text description. Annotations, new in Oracle AI Database 26ai, hold any number of name-value pairs on tables, columns, views, indexes, domains, and other objects, stored in the data dictionary for any tool to read.

Code for This Guide

The main examples are in the examples/tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create table t (column type annotations (name ['value'] [, ...]), ...)
  annotations (name ['value'] [, ...])

alter table t annotations ([add | drop | replace] name ['value'] [, ...])
alter table t modify (column annotations ([add | drop | replace] name ['value'] [, ...]))

comment on {table t | column t.column} is 'text'

The view USER_ANNOTATIONS_USAGE lists annotations by object and column.

Comments and Annotations on an Existing Table

The example uses a FARE_RULES table, created first:

Example:

create table fare_rules (
  rule_id       number        constraint fare_rules_pk primary key,
  route_id      number        not null constraint fare_rules_route_fk references routes,
  cabin         varchar2(8)   default 'ECONOMY' not null,
  valid_from    date          not null,
  valid_to      date,
  base_fare     number(8,2)   not null,
  refundable    boolean       default false,
  notes         varchar2(200),
  constraint fare_rules_cabin_ck check (cabin in ('BUSINESS', 'ECONOMY')),
  constraint fare_rules_dates_ck check (valid_to is null or valid_to > valid_from),
  constraint fare_rules_uk unique (route_id, cabin, valid_from)
);

insert into fare_rules (rule_id, route_id, valid_from, base_fare)
values (1, 1, date '2026-04-01', 520);

select rule_id, route_id, cabin, valid_from, base_fare, refundable from fare_rules;

Output:

Table FARE_RULES created.

1 row inserted.

   RULE_ID    ROUTE_ID CABIN      VALID_FROM        BASE_FARE REFUNDABLE
__________ ___________ __________ ______________ ____________ _____________
         1           1 ECONOMY    01-APR-2026             520 false

It then adds comments, two table annotations, and two column annotations, and lists them.

Example:

comment on table fare_rules is 'Base fares by route, cabin, and validity period';
comment on column fare_rules.base_fare is 'Fare in USD before taxes';

alter table fare_rules annotations (owner 'Revenue Management', review_cycle 'quarterly');
alter table fare_rules
  modify (base_fare annotations (unit 'USD', display_label 'Base fare'));

select object_name, column_name, annotation_name, annotation_value
from   user_annotations_usage
where  object_name = 'FARE_RULES'
order  by column_name nulls first, annotation_name;

Output:

Comment created.

Comment created.

Table FARE_RULES altered.

Table FARE_RULES altered.

OBJECT_NAME    COLUMN_NAME    ANNOTATION_NAME    ANNOTATION_VALUE
______________ ______________ __________________ _____________________
FARE_RULES                    OWNER              Revenue Management
FARE_RULES                    REVIEW_CYCLE       quarterly
FARE_RULES     BASE_FARE      DISPLAY_LABEL      Base fare
FARE_RULES     BASE_FARE      UNIT               USD

Annotations in CREATE TABLE

Annotations can be declared with the table, and later changed with ADD, DROP, and REPLACE.

Example:

create table seat_maps (
  type_code varchar2(4) annotations (display_label 'Aircraft type'),
  seat      varchar2(4) annotations (display_label 'Seat', ui_hint 'monospace'),
  extra_legroom boolean
) annotations (owner 'Ground Operations');

alter table seat_maps annotations (drop owner, add steward 'Fleet Planning');

select column_name, annotation_name, annotation_value
from   user_annotations_usage
where  object_name = 'SEAT_MAPS'
order  by column_name nulls first, annotation_name;

Output:

Table SEAT_MAPS created.

Table SEAT_MAPS altered.

COLUMN_NAME    ANNOTATION_NAME    ANNOTATION_VALUE
______________ __________________ ___________________
               STEWARD            Fleet Planning
SEAT           DISPLAY_LABEL      Seat
SEAT           UI_HINT            monospace
TYPE_CODE      DISPLAY_LABEL      Aircraft type

The owner annotation was dropped and a steward added. Column annotations such as display_label and ui_hint are the kind of metadata a form generator or reporting tool can use directly. Drop the work table afterward with drop table seat_maps purge.

Things to Know

• An annotation without a value works as a flag, such as annotations (sensitive).
• Annotation names are case-insensitive unless quoted, and values are text.
• Domains can carry annotations too, which every column of the domain then shares.

Related Guides

• Oracle DOMAIN_ Functions

Conclusion

Annotations store name-value metadata on tables, columns, and other objects, alongside the single free-text comment. Declare them in CREATE TABLE or add them with ALTER, change them with ADD, DROP, and REPLACE, and read them from USER_ANNOTATIONS_USAGE.