In our posting “Oracle Database 23 Free Developer Release – 10 features you should know” my colleagues and I introduced 10 interesting SQL features. Now we want to deepen some of these features and give some more details and background information. In this posting, I’d like to focus on the SQL feature schema annotations, give some examples, and show how to monitor them.

What are schema annotations and how can they be used? In general, schema annotations (also called annotations) as the name suggests are additional property metadata for database objects. They are centrally stored in the database so that you can share metadata information across applications, modules, and microservices. You can use them to customize business logic, and user interfaces or provide information about your database objects to your metadata repositories.
Note: With Oracle Database RU 19.28, it’s also available in 19c. 

Like comments, schema annotations are optional. More precisely, a schema annotation is either a name-value pair or a name by itself. The name is mandatory, the value optional, and both can be provided as freeform text fields. But what about comments? What is the difference between schema annotations and comments? Schema annotations have a broader scope than comments. For example, annotations are available for different kinds of database objects, which means not only for tables. Comments do not have a name, they have only a freeform value and you cannot add multiple comments for the same object. 

Let’s show some examples to get an idea, of how they can be used.
Schema annotations can be generated with CREATE or ALTER statements. To add or drop annotations, you require the CREATE or ALTER privilege on the schema object for which the annotation is specified.
In general, schema annotations look like

ANNOTATIONS ([ADD|DROP] annotation_name [ annotation_value ] [ , annotation_name [ annotation_value  ]... )

Refer to the documentation to find details about Supported Database Objects.

Let’s create an annotated table EMP_ANNOTATED with column and table schema annotations.

drop table if exists emp_annotated; -- with new 23 "if exists" syntax 

create table emp_annotated
( empno number,
  ename varchar2(50) annotations (display 'lastname'),
  salary number      annotations (person_salary, column_hidden)
 )
annotations (display 'employees'); 

Names and values can be character string literals that can hold up to 4000 characters. In the first example person_salary, colum_hidden, and display are names; ‘last_name’ and ’employees’ are values. If you need to use reserved words or white characters, enclose them in double-quotes. 

You can add or drop schema annotations. For example, let’s drop the column annotation display from the column ENAME. 

alter table emp_annotated modify ename annotations (drop display);

Not only tables and columns are supported but also views and view columns, materialized views and materialized view columns, indexes, and also the new SQL object domains and multi-column domain columns. In the next step let’s create an annotated view EMPDEPT_ANN.  

create or replace view empdept_ann
   (emp_id    annotations (Identity, display 'employee Id', category 'emp info'),
    emp_name  annotations (display 'employee name', category 'emp info'),
    emp_dname annotations (category 'emp info'))
    annotations (title 'employee view')
    as select e.empno, e.ename, d.dname from emp e, dept d
       where e.deptno=d.deptno and sal>1000;

Now let’s create an index on the table DEPT and the column DEPTNO with the annotation value-pair display ‘Deptno Index’. 

create index i_dept_id on dept (deptno) annotations (display 'Deptno Index');
Index created.

In the last step let’s monitor schema annotations we just created. Query the data dictionary views USER_ANNOTATIONS and USER_ANNOTATIONS_USAGE (also with the prefix ALL/DBA)  to track the list of schema annotations and their usage across your schema objects. 

To obtain object-level schema annotations for table, index, domain, and views: 

set lines 200
set pages 200
col object_name format a25
col object_type format a15
col annotation_name format a15
col annotation_value format a15
col column_name format a20

select object_name, object_type, annotation_name, annotation_value 
from user_annotations_usage
where column_name is null order by 2,1;

OBJECT_NAME               OBJECT_TYPE     ANNOTATION_NAME ANNOTATION_VALU
------------------------- --------------- --------------- ---------------
YEARBIRTH                 DOMAIN          TITLE           yearformat
I_DEPT_ID                 INDEX           DISPLAY         Deptno Index
EMP_ANNOTATED             TABLE           DISPLAY         employees
PERSON                    TABLE           DISPLAY         person_table
EMPDEPT_ANN               VIEW            TITLE           employee view
5 rows selected.

To obtain column-level schema annotations:

select object_name, object_type, column_name, annotation_name, annotation_value 
from user_annotations_usage
where column_name is not null order by 2,1;

OBJECT_NAME               OBJECT_TYPE     COLUMN_NAME          ANNOTATION_NAME ANNOTATION_VALU
------------------------- --------------- -------------------- --------------- ---------------
EMP_ANNOTATED             TABLE           SALARY               COL_HIDDEN
EMP_ANNOTATED             TABLE           SALARY               PERSON_SALARY
PERSON                    TABLE           PERSON_BIRTH         TITLE           yearformat
EMPDEPT_ANN               VIEW            EMP_NAME             CATEGORY        emp info
EMPDEPT_ANN               VIEW            EMP_DNAME            CATEGORY        emp info
EMPDEPT_ANN               VIEW            EMP_NAME             DISPLAY         employee name
EMPDEPT_ANN               VIEW            EMP_ID               DISPLAY         employee Id
EMPDEPT_ANN               VIEW            EMP_ID               IDENTITY
EMPDEPT_ANN               VIEW            EMP_ID               CATEGORY        emp info
9 rows selected.

And now let’s display the column schema annotations as a single JSON collection per column:

select object_type, object_name, column_Name, JSON_ARRAYAGG(JSON_OBJECT(Annotation_Name,Annotation_Value)) in_jsonformat 
from user_annotations_usage
where column_name is not null group by object_type, object_name, column_name;

OBJECT_TYPE     OBJECT_NAME               COLUMN_NAME
--------------- ------------------------- --------------------
IN_JSONFORMAT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------
VIEW            EMPDEPT_ANN               EMP_ID
[{"Annotation_Name":"IDENTITY","Annotation_Value":null},{"Annotation_Name":"CATEGORY","Annotation_Value":"emp info"},{"Annotation_Name":"DISPLAY","Annotation_Value":"employee Id"}]
VIEW            EMPDEPT_ANN               EMP_NAME
[{"Annotation_Name":"DISPLAY","Annotation_Value":"employee name"},{"Annotation_Name":"CATEGORY","Annotation_Value":"emp info"}]
VIEW            EMPDEPT_ANN               EMP_DNAME
[{"Annotation_Name":"CATEGORY","Annotation_Value":"emp info"}]
TABLE           PERSON                    PERSON_BIRTH
[{"Annotation_Name":"TITLE","Annotation_Value":"yearformat"}]
TABLE           EMP_ANNOTATED             SALARY
[{"Annotation_Name":"PERSON_SALARY","Annotation_Value":null},{"Annotation_Name":"COL_HIDDEN","Annotation_Value":null}]
5 rows selected.

Further Reading