Oracle | ANNOTATIONS

Oracle Anotations Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 23c)

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 23c)

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 23c)

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);