Oracle Tagging for Objects

Matheus Boesing

It's more than comments, it consists in add indexable keywords that enable easily locate tables or other objects in database, like Mac OS and other systems allow to tag files.

The tags could have some kind of keyword for object use. For example: “Transactional”, “Report”, “Log”, “Backup”, “Auxiliary”, “Internal”, “Depracated” (for application purpose), etc.

Example of Syntax:

CREATE TABLE example (a NUMBER, b NUMBER) TAGS ('Transactional'[,…]);

CREATE SEQUENCE seq_exemp ON example TAGS ('Transactional');

CREATE INDEX idx_oltp ON example TAGS ('Transactional');

CREATE INDEX idx_report ON example TAGS ('Report');

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, TAG FROM DBA_TAGS WHERE TAG=’Transactional’;

OBJECT_NAME     OBJECT_TYPE  TAG

-------------   ----------   -------------

EXAMPLE         TABLE        TRANSACTIONAL

SEQ_EXEMP       SEQUENCE     TRANSACTIONAL

IDX_OLTP        INDEX        TRANSACTIONAL

And the TAGs could be reseted (erase all values and put new ones), added, removed, or replaced like:

ALTER INDEX idx_oltp INVISIBLE [RESET|ADD|REMOVE|REPLACE] TAGS('Deprecated');

These tags could be let “free to use”, so each development team, or company could implement his own tags. Pre-defined tags like suggested be created “by default”.

Companies can create tags for his developers like “TAGS ('Transactional', 'John');”. So this object will have the Tags for Transacional Object and is maintained by John. If John is leaving the company, is simply to list his “Objects” to make a KT to other teammates, or something like that.

Actually companies have lots of parallel controls for that.

And, I think, it’s a very useful not “linear” way to index/organize the objects that can helps in all development lifecycle and DBA (that occasionally have no access to application ER).

Mike Kutz

TAG using JSON value might be more flexible.

This would then allow template based code generators (eg ) to automatically identify which tables need to be combined together to create a specific Transactional API (XAPI).  (eg an API that must work with >1 tables)

MK

Jeffrey Kemp

Tags for table columns, constraints, etc. would also be useful.

Matheus Boesing

TAG using JSON value might be more flexible.

This would then allow template based code generators (eg ) to automatically identify which tables need to be combined together to create a specific Transactional API (XAPI).  (eg an API that must work with >1 tables)

MK

Great Mike!
It really should be done with JSON syntax. It allows to create different kinds of tags in a single 'tag column', like:
[...]TAGS ('{Use:Transactional, SensitiveData:false, Version1.0:MatheusDBA, Version2.0:AnotherDBA}');

Or even tags hierarchies/subtypes... Anyway, it's way more flexible.
Thanks for this insight!

Matheus Boesing

Tags for table columns, constraints, etc. would also be useful.

Exactly... I just used some kind of objects for this example... but I believe it can apply for any king of object, including schemas... like (using Mike's contribution):
CREATE USER USER_EXAMPLE TAGS ('{Use:AdHocReports, Accountable:InternalBusinessTeam}');

Gbenga Ajakaye

This would definitely be useful.