Is it possible to add a metadata field at column-level (in the Oracle Data Dictionary)?

The purpose would be to hold a flag identifying where individual data items in a table have been anonymised.

I'm an analyst (not a DBA) and I'm using Oracle SQL Developer which surfaces (and enables querying of) the COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_DEFAULT, COLUMN_ID, and COMMENTS metadata fields of our Oracle DB (see pic).

I'd be looking to add another metadata field at this level (essentially, to add a second 'COMMENTS' field) to hold the 'Anonymisation' flag, to support easy querying of our flagged-anonymised data.

If it's possible (and advisable / supportable), I'd be grateful for any advice for describing the steps required to enable this, which I can then discuss with our Developer and DBA.

CodePudding user response：

[TL;DR] Don't do it. Find another way.

If it's advisable

NO

Never modify the data dictionary; (unless Oracle support tells you to) you are likely to invalidate your support contract with Oracle and may break the database and make it unusable.

If it's possible

Don't do this.

If you really want to try it then still don't.

If you really, really want to try it then find a database you don't care about (the don't care about bit is important!) and log on as a SYSDBA user and:

ALTER TABLE whichever_data_dictionary_table ADD anonymisation_flag VARCHAR2(10);

Then you can test whether the database breaks (and it may not break immediately but at some point later), but if it does then you almost certainly will not get any support from Oracle in fixing it.

Did we say, "Don't do it"... we mean it.

CodePudding user response：

As you already know, you shouldn't do that.

But, nothing prevents you from creating your own table which will contain such an info.

For example:

SQL> CREATE TABLE my_comments
  2  (
  3     table_name      VARCHAR2 (30),
  4     column_name     VARCHAR2 (30),
  5     anonymisation   VARCHAR2 (10)
  6  );

Table created.

Populate it with some data:

SQL> insert into my_comments (table_name, column_name)
  2    select table_name, column_name
  3    from user_tab_columns
  4    where table_name = 'DEPT';

3 rows created.

Set the anonymisation flag:

SQL> update my_comments set anonymisation = 'F' where column_name = 'DEPTNO';

1 row updated.

When you want to get such an info (along with some more data from user_tab_columns, use (outer) join:

SQL> select u.table_name, u.column_name, u.data_type, u.nullable, m.anonymisation
  2  from user_tab_columns u left join my_comments m on m.table_name = u.table_name
  3                                                 and m.column_name = u.column_name
  4  where u.column_name = 'DEPTNO';

TABLE_NAME COLUMN_NAME     DATA_TYPE    N ANONYMISATION
---------- --------------- ------------ - ---------------
DEPT       DEPTNO          NUMBER       N F
DSV        DEPTNO          NUMBER       N
DSMV       DEPTNO          NUMBER       Y
EMP        DEPTNO          NUMBER       Y

SQL>

Advantages: you won't break the database & you'll have your additional info.

Drawbacks: you'll have to maintain the table manually.