Annotations (introduced in March’s post) become particularly powerful in enterprise environments where data governance, self-service tooling, and schema documentation are critical. Let’s look at advanced patterns.
Data classification for security:
ALTER TABLE customers MODIFY (
ssn ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'Critical', ADD EncryptionRequired 'true'),
email ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'High'),
birth_date ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'Medium'),
customer_id ANNOTATIONS (ADD DataClass 'Internal')
);A data catalog tool can query USER_ANNOTATIONS_USAGE to automatically generate a data classification report without anyone maintaining a separate spreadsheet.
Deprecation management:
ALTER TABLE legacy_orders ANNOTATIONS (ADD Deprecated 'true', ADD DeprecatedSince '2024-01-01', ADD MigratedTo 'orders_v2');CI/CD pipelines can query for deprecated objects and fail builds that reference them.
UI metadata for low-code applications:
ALTER TABLE employees MODIFY (
first_name ANNOTATIONS (ADD UILabel 'First Name', ADD DisplayOrder '1', ADD UIRequired 'true'),
last_name ANNOTATIONS (ADD UILabel 'Last Name', ADD DisplayOrder '2', ADD UIRequired 'true'),
salary ANNOTATIONS (ADD UILabel 'Annual Salary', ADD UIReadOnly 'true', ADD UIFormat 'currency')
);Low-code platforms like Oracle APEX can read these annotations to auto-generate forms with the correct labels, ordering, and validation.
Queryable catalog:
-- Find all columns marked as PII across the schema
SELECT owner, object_name, column_name, annotation_value AS data_class
FROM dba_annotations_usage
WHERE annotation_name = 'DataClass'
AND annotation_value = 'PII'
ORDER BY owner, object_name;Annotations turn the Oracle data dictionary into a live, queryable governance layer — no external catalog required.
