SQL Annotations: Adding Metadata to Your Database Objects

Oracle 23ai introduces Annotations — a way to attach custom metadata to database objects and columns directly in the database, not in a separate spreadsheet or wiki page that goes out of date.

An annotation is a name-value pair (or just a name) attached to a table, column, index, view, or other object. Think of it like tags or labels on cloud resources, but for your schema objects.

Adding annotations when creating a table:

CREATE TABLE employees (
    employee_id  NUMBER         ANNOTATIONS (PrimaryIdentifier, DisplayOrder '1'),
    first_name   VARCHAR2(50)   ANNOTATIONS (UserFacing 'true', MaxLength '50'),
    salary       NUMBER(10,2)   ANNOTATIONS (Sensitive 'true', Audited 'true'),
    dept_code    VARCHAR2(10)   ANNOTATIONS (ForeignKey 'departments.dept_code')
)
ANNOTATIONS (BusinessDomain 'HumanResources', Owner 'HR Team');

Adding annotations to an existing object:

ALTER TABLE employees
    ANNOTATIONS (ADD Deprecated 'true', ADD MigratedFrom 'LEGACY_EMP');

Querying annotations:

SELECT object_name, column_name, annotation_name, annotation_value
FROM   user_annotations_usage
ORDER BY object_name, column_name;

Practical use cases:

• Mark columns as Sensitive for data classification tools
• Tag tables with BusinessDomain for impact analysis
• Add Owner metadata for accountability
• Flag deprecated objects with Deprecated so tooling can warn users
• Store application-specific metadata like UILabel or DisplayOrder

Before annotations, metadata like this lived in comments (fragile, hard to query), separate tables (manual sync), or external tools. Annotations make it a first-class citizen of the schema.

This feature also has a personal story for me — one I’ll be sharing in detail in September. Stay tuned.