Annotations in Oracle Database 23ai

In Oracle Database versions prior to 23ai, the Comment functionality allowed users to add descriptive information for objects such as tables, table columns, materialized views, and views. However, this feature had certain limitations: All comments were stored as a single, lengthy text. There was no option to categorize the comments. Oracle 23ai introduces the Annotation feature, which provides similar functionality to comments but with additional capabilities. Using Annotations, users can add structured and categorized descriptions to objects like tables, views, materialized views, indexes, and columns, based on name-value pairs. Key Features of Annotations: Flexibility: At least one Annotation_name is mandatory for an annotation. Multiple Annotation_name and Annotation_value pairs can be defined for each object. Defining Annotation_value is optional. Naming Freedom: There are no restrictions on how Annotation_name can be named. Example of Using Annotations in Oracle 23ai Creating a Table with Annotations: SQL> CREATE TABLE STD ( id NUMBER ANNOTATIONS (DESCRIPTION1 'student ID', DESCRIPTION2 'Student code'), name VARCHAR2(20) ANNOTATIONS (DESCRIPTION 'Full Name for students') ) ANNOTATIONS (DESCRIPTION 'Student Table'); This creates a table STD with annotated columns and a general description. Viewing Annotations: Annotations can be queried from the USER_ANNOTATIONS_USAGE view: SQL> SELECT object_name, column_name, annotation_name, annotation_value FROM USER_ANNOTATIONS_USAGE; | OBJECT\_NAME | COLUMN\_NAME | ANNOTATION\_NAME | ANNOTATION\_VALUE | | ------------ | ------------ | ---------------- | ---------------------- | | STD | | DESCRIPTION | Student Table | | STD | ID | DESCRIPTION1 | student ID | | STD | ID | DESCRIPTION2 | Student code | | STD | NAME | DESCRIPTION | Full Name for students | Additional views such as USER_ANNOTATION_VALUES and USER_ANNOTATIONS provide summarized information. Modifying Annotations: Annotations can be removed or added using the ALTER statement: Removing an Annotation: SQL> ALTER TABLE STD MODIFY (id ANNOTATIONS (DROP DESCRIPTION1)); Adding New Annotations: SQL> ALTER TABLE STD ANNOTATIONS (ADD name1 'value1', name2 'value2'); Working with Views: Annotations can also be applied to views. For example: SQL> CREATE OR REPLACE VIEW V1 (test1 ANNOTATIONS (name1 'value1'), test2 ANNOTATIONS (name2 'value2')) AS SELECT 'test1', 'test2' FROM dual; Query the annotations: SQL> SELECT object_name, column_name, annotation_name, annotation_value FROM USER_ANNOTATIONS_USAGE WHERE object_name = 'V1'; | OBJECT\_NAME | COLUMN\_NAME | ANNOTATION\_NAME | ANNOTATION\_VALUE | | ------------ | ------------ | ---------------- | ----------------- | | V1 | TEST1 | NAME1 | value1 | | V1 | TEST2 | NAME2 | value2 | Conclusion The Annotation feature in Oracle 23ai enhances database documentation by allowing structured and organized metadata to be attached to database objects. This improves the clarity and manageability of object descriptions, especially in complex database schemas.

May 3, 2025 - 18:11

In Oracle Database versions prior to 23ai, the Comment functionality allowed users to add descriptive information for objects such as tables, table columns, materialized views, and views. However, this feature had certain limitations:

All comments were stored as a single, lengthy text.
There was no option to categorize the comments.

Oracle 23ai introduces the Annotation feature, which provides similar functionality to comments but with additional capabilities. Using Annotations, users can add structured and categorized descriptions to objects like tables, views, materialized views, indexes, and columns, based on name-value pairs.

Key Features of Annotations:

Flexibility:

At least one Annotation_name is mandatory for an annotation.
Multiple Annotation_name and Annotation_value pairs can be defined for each object.
Defining Annotation_value is optional.

Naming Freedom:

There are no restrictions on how Annotation_name can be named.

Example of Using Annotations in Oracle 23ai

Creating a Table with Annotations:

SQL> CREATE TABLE STD 
(
  id NUMBER ANNOTATIONS (DESCRIPTION1 'student ID', DESCRIPTION2 'Student code'),
  name VARCHAR2(20) ANNOTATIONS (DESCRIPTION 'Full Name for students')
)
ANNOTATIONS (DESCRIPTION 'Student Table');

This creates a table STD with annotated columns and a general description.

Viewing Annotations:
Annotations can be queried from the USER_ANNOTATIONS_USAGE view:

SQL> SELECT object_name, column_name, annotation_name, annotation_value 
FROM USER_ANNOTATIONS_USAGE;
| OBJECT\_NAME | COLUMN\_NAME | ANNOTATION\_NAME | ANNOTATION\_VALUE      |
| ------------ | ------------ | ---------------- | ---------------------- |
| STD          |              | DESCRIPTION      | Student Table          |
| STD          | ID           | DESCRIPTION1     | student ID             |
| STD          | ID           | DESCRIPTION2     | Student code           |
| STD          | NAME         | DESCRIPTION      | Full Name for students |

Additional views such as USER_ANNOTATION_VALUES and USER_ANNOTATIONS provide summarized information.

Modifying Annotations:
Annotations can be removed or added using the ALTER statement:

Removing an Annotation:

SQL> ALTER TABLE STD MODIFY (id ANNOTATIONS (DROP DESCRIPTION1));

Adding New Annotations:

SQL> ALTER TABLE STD ANNOTATIONS (ADD name1 'value1', name2 'value2');

Working with Views:
Annotations can also be applied to views. For example:

SQL> CREATE OR REPLACE VIEW V1 
  (test1 ANNOTATIONS (name1 'value1'),
   test2 ANNOTATIONS (name2 'value2'))
AS
SELECT 'test1', 'test2' FROM dual;

Query the annotations:

SQL> SELECT object_name, column_name, annotation_name, annotation_value 
FROM USER_ANNOTATIONS_USAGE 
WHERE object_name = 'V1';
| OBJECT\_NAME | COLUMN\_NAME | ANNOTATION\_NAME | ANNOTATION\_VALUE |
| ------------ | ------------ | ---------------- | ----------------- |
| V1           | TEST1        | NAME1            | value1            |
| V1           | TEST2        | NAME2            | value2            |

Conclusion
The Annotation feature in Oracle 23ai enhances database documentation by allowing structured and organized metadata to be attached to database objects. This improves the clarity and manageability of object descriptions, especially in complex database schemas.