Our Oracle environment is around 25 years old. The data structure has historically been managed by many (non-dba) programmers throughout those years. Data integrity, naming standards, performance, metadata and overall general relational database concepts were not taken into consideration throughout the years. Now we have thousands of objects that are used across multiple teams, affecting multiple applications, interfaces, tasks, and reports. We are in a VERY SLOW process to work through all of these issues. This process will include changing column names, adding constraints, triggers, maybe even changing object names. People don't like change, so establishing a new set of standards and guidelines isn't an overnight thing. I'm looking to establish a baseline of objects and, for tables and views, column data. I will then build a custom application to help folks follow those new standards, "automating" as much as possible to identify where they are lacking to help this become more routine. A very small part of this picture is to track when objects are being changed. We do not want to turn auditing on in our Oracle environment simply for this purpose given the additional storage necessary and (even if minimal) performance hit.
All of that said, for this very small piece of the puzzle (and it's not that important, but important enough that I'd like to do it), I'm attempting to track if a column name changes. I cannot track those changes by keying off the column name. I don’t think it exists but worth a shot asking. I’m looking for a unique identifier column for table/view columns in Oracle. For example, DBA_OBJECTS.OBJECT_ID is a unique identifier for objects themselves so if an object name changes, I can key off that.
DBA_TABS_COLUMNS.COLUMN_ID is only unique to the specific table and can change as you drop columns.
I'm open to other suggestions other than auditing and my ask above if folks have any that would fall within the overall process that I'm taking (custom auditing with tables, views and an application to manage change).
Thanks for any assistance!
If you want something like OBJECT_ID for objects or the ROWID pseudocolumn for rows but, instead, for columns then COLUMN_ID is the closest match as it is auto-generated and unique for each table.
If you want something that is unique across all tables then a composite key comprised of either:
schema_name.table_name.column_name; orschema_name.table_name.column_id.You said:
COLUMN_ID[...] can change as you drop columns.
Generally, columns are not dynamically dropped and re-created so this is not a problem that you need to be concerned about. However, if you are trying to persist a unique identifier then you will need to update your records when the table definitions are modified (and that may be a sign that you are doing something atypical and need to re-evaluate why you are making so many dynamic changes to the database and trying to dynamically collect unique column references).
Regarding the edit to the question:
I cannot track those changes by keying off the column name. I don’t think it exists but worth a shot asking. I’m looking for a unique identifier column for table/view columns in Oracle.
It does not exist within Oracle's data dictionary.
You can uniquely reference a column within a table by its name or its COLUMN_ID until a DDL statement renames one of the columns. In which case, either the name or the COLUMN_ID are likely to change.
There is no auto-generated column that can track a column when it is being renamed and the order of the columns changes.