How can I see where data in a column comes from?

I don't know if anyone can help me. In my job, I inherited a completely undocumented database (Oracle 11). So far, I've managed to map most of the tables and determine what's going on where. However, there are a few columns that I haven't been able to decipher.

Is there some way of finding out how is the data in the column built? This is not a manual input. Everything seems to point to the data being the result of an entry in a different column in a completely different t开发者_如何学Goable.

It might be an impossible task, but any and all suggestions will be more than welcome.

Thanks!

C

Perhaps the data is being inserted in your mystery columns via a trigger? Try looking in the PL/SQL source table in the dictionary:

SELECT owner, name, type, line
  FROM dba_source
 WHERE UPPER(text) LIKE '%MYSTERY_COLUMN_NAME%'
   AND type = 'TRIGGER';  -- use or omit this as desired.

This will get you pointed in some possible places to look.

Good luck!

You can retrieve the complete DDL for a table using the DBMS_METADATA package.

SELECT dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME', 'YOUR_USER_NAME')
FROM dual;

If those columns are really computed columns then this should be visible in the DDL for the table.

Alternatively you can use SQL Developer to show the DDL for the table

I am presuming that you already have the sql that is in question.

select table_name from dba_tab_columns
where column_name = 'COLUMN_YOU_WANT_TO_KNOW'

will provide all tables that contain a column name that you are looking for. If you do not have dba privileges you can use all_tab_columns instead (which will show all tables your account would have access to).