What Are Annotations and How to Use Them in Oracle 23c?

One of the new features of the Oracle 23c is annotations. These were mostly created for developers, but I can see a great use for them for DBAs as well. Let’s have a look at what annotations are, how to use them, and how can you benefit as a DBA!

What are Annotations?

Let’s get this first and remember: annotations are not new database object types! Annotations are additional metadata information about database objects. Think about them as a description or an explanation for the object. You can use annotations on different type of objects: tables, table columns, views, view columns, mat views and mat view columns, indexes, domains, and multi-column domain columns. The purpose of annotations is for helping developers with object usage properties, by sharing and documenting metadata information about the application. Annotations are stored in the data dictionary, and can be viewed through data dictionary views. Annotations have no impact on the objects they are linked to, they are not interpreted by the database in any way.

Why would you use annotations?

You would use annotations for documentation purposes, to provide explanations. You could add meaning to the columns or tables by adding different properties to better describe the user interfaces or customize the application logic. You could document the purpose of a table, a view or column, who should have access to it, what information is stored in the table and so on.

How to use annotations?

Annotations can be added during object creation with the CREATE statement, or after object creation with an ALTER statement. An annotation has a name (up to 1024 characters), which is mandatory, and a value (up to 4000 characters), which is optional. Both of these are freeform text fields, meaning these could have any value. The only constraint is that reserved words or white characters in annotation name, must be enclosed in double quotes as in “Primary Key”. You can have multiple annotations for the same object, as a result, you can keep adding annotations, or dropping annotations. In order to add annotations to an object, you must own the object or have ALTER privileges on the object. Let’s look at some examples of working with annotations!

--adding and dropping annotations on table/columns

CREATE TABLE PROJECTS (
  PROJ_ID NUMBER,
  PROJ_NAME VARCHAR2(30),
  MGR_NAME  VARCHAR2(30)
  )
  ANNOTATIONS (Classification 'Project Info Table', Visibility 'All');

ALTER TABLE PROJECTS MODIFY (PROJ_ID annotations (PrimaryKey, Visibility 'All'));
ALTER TABLE PROJECTS MODIFY (PROJ_NAME annotations (Description 'Full Project Name'));
ALTER TABLE PROJECTS MODIFY (MGR_NAME annotations (Description 'Project Manager Full Name'));

ALTER TABLE PROJECTS annotations (drop CLASSIFICATION);
ALTER TABLE PROJECTS MODIFY (MGR_NAME annotations (drop DESCRIPTION));

A great use for annotations for DBAs is for indexes! You can now document right in the database why you created the index, and the reason will no longer get buried in an old ticket or documentation.

CREATE INDEX PROJECTS_IDX_PK on PROJECTS (PROJ_ID) ANNOTATIONS (PURPOSE 'Primary Key unique index');
CREATE INDEX PROJECTS_IDX_01 on PROJECTS (MGR_NAME) ANNOTATIONS (PURPOSE 'Used by the Weekly Report on Assigned PMs, created by DR on 20231110 Ticket#12345');

ALTER INDEX PROJECTS_IDX_PK ANNOTATIONS(DROP PURPOSE);

Where do you find information about annotations?

You can query the data dictionary to find information about annotations, and see what annotations have been created on objects. As a DBA you would query the DBA_* views, however developers would use the USER_* views: DBA_ANNOTATIONS, DBA_ANNOTATIONS_USAGE and DBA_ANNOTATIONS_VALUES.

Have you used this feature so far? What are your thoughts on it? Let me know in the comments section!