Oracle | ANNOTATIONS

Oracle Annotations Version 23ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Annotations are an extension of database comments. With comments we were able to add free text to objects such as tables and columns, allowing us to describe their purpose and usage. Annotations take this a step further, allowing us to associate name-value pairs with most database objects, that can be used to describe or classify them.

You may see annotations described as "application usage annotations". This simply emphasizes that annotations are intended to help application documentation but more likely guarantees that they will be almost universally ignored because DBAs and Developers are genetically resistant to anything perceived as creating structure.

Data Dictionary Objects and Dependencies

ALL_ANNOTATIONS	I_GG_PROCEDURE_ANNOTATION
ALL_ANNOTATIONS_USAGE	KU$_ANNOTATION_LIST_T
ALL_ANNOTATION_TEXT_METADATA	KU$_ANNOTATION_T
ALL_ANNOTATION_VALUES	KU$_ANNOTATION_VIEW
ANNOTATIONS_USAGE$	SDO_ANNOTATION_TEXT_METADATA
CDB_ANNOTATIONS	ST_ANNOTATIONTEXTELEMENT
CDB_ANNOTATIONS_USAGE	ST_ANNOTATIONTEXTELEEMENT_ARRAY
CDB_ANNOTATION_VALUES	ST_ANNOTATION_TEXT
CDB_GG_PROCEDURE_ANNOTATION	USER_ANNOTATIONS
DBA_ANNOTATIONS	USER_ANNOTATIONS_USAGE
DBA_ANNOTATIONS_USAGE	USER_ANNOTATION_TEXT_METADATA
DBA_ANNOTATION_VALUES	USER_ANNOTATION_VALUES
DBA_GG_PROCEDURE_ANNOTATIONS	XDB$ANNOTATION_LIST_T
DBMS_XMLSCHEMA_ANNOTATE	XDB$ANNOTATION_T
GG$_PROCEDURE_ANNOTATION

Related System Privileges Annotation requires the privilege associated with object ownership

Create Annotation (new 23ai)

Create Object with Annotation CREATE [OR REPLACE] <object_type> [schema_name.] <object_name> ( <column_name> <data_type> [DEFAULT <default_value> | NOT NULL> ANNOTATIONS ( ADD [IF NOT EXISTS] <annotation_name> {'<annotation_value>'} {,});

CREATE TABLE heap_table1 (

      person_id   NUMBER ANNOTATIONS (ADD SingleColumn 'Test Value'));

      

      CREATE TABLE heap_table2 (

      person_id   NUMBER       ANNOTATIONS (ADD PrimaryKey 'Test Value'),

      person_name VARCHAR2(50) ANNOTATIONS (ADD PII        'Audit Access'),

      system_date DATE);

      

      col annotation_owner format a30

      col annnotation_name format a30

      

      SELECT * FROM dba_annotations;

      

      ANNOTATION_OWNER  ANNOTATION_NAME

      ----------------- ----------------

      UWCLASS           SINGLECOLUMN

      UWCLASS           PRIMARYKEY

      UWCLASS           PII

      

      col annotation_value format a30

      

      SELECT object_name, column_name, annotation_value

      FROM dba_annotations_usage

      ORDER BY 1,2;

      

      OBJECT_NAME  COLUMN_NAME  ANNOTATION_VALUE

      ------------ ------------ -------------------

      HEAP_TABLE1  PERSON_ID    Test Value

      HEAP_TABLE2  PERSON_ID    Sequence Generated

      HEAP_TABLE2  PERSON_NAME  Audit Access

Alter Annotation (new 23ai)

Add/Modify Object Annotation ALTER <object_type> [<schema_name>.] <object_name> ANNOTATIONS (<annotation_value>);

ALTER TABLE heap_table1 ANNOTATIONS 
      (SingleColumn 'New Annotation');

      

      SELECT object_name, column_name, annotation_value

      FROM dba_annotations_usage

      ORDER BY 1,2 NULLS FIRST;

      

      OBJECT_NAME  COLUMN_NAME  ANNOTATION_VALUE

      ------------ ------------ -------------------

      HEAP_TABLE1               New Annotation

      HEAP_TABLE1  PERSON_ID    Test Value

      HEAP_TABLE2  PERSON_ID    Sequence Generated

      HEAP_TABLE2  PERSON_NAME  Audit Access

      HEAP_TABLE2  PERSON_NAME  Date Row Inserted

Add/Modify Column Annotation ALTER <object_type> [<schema_name>.] <object_name> MODIFY (<column_name> ANNOTATIONS (<annotation_value>);

ALTER TABLE heap_table1 MODIFY person_id ANNOTATIONS (REPLACE SingleColumn 'Diff Value');

      

      SELECT object_name, column_name, annotation_value

      FROM dba_annotations_usage

      WHERE objeect_name = 'HEAP_TABLE1'

      ORDER BY 1,2 NULLS FIRST;

      

      OBJECT_NAME  COLUMN_NAME  ANNOTATION_VALUE

      ------------ ------------ -------------------

      HEAP_TABLE1               New Annotation

      HEAP_TABLE1  PERSON_ID    Diff Value

Drop Annotation (new 23ai)

Drop In-Line Annotation CREATE [OR REPLACE] <object_type> [schema_name.] <object_name> ( <column_name> <data_type> [DEFAULT <default_value> | NOT NULL> ANNOTATIONS ( DROP [NOT EXISTS] <annotation_name> {'<annotation_value>'} {,});

ALTER TABLE heap_table1 ANNOTATIONS (DROP IF EXISTS SingleColumn);