Which Oracle table uses a sequence?

Question

Having a sequence, I need to find out which table.column gets its values. As far as I know, Oracle doesn't keep track of this relationship. So, looking up for the sequence in source code would be the only way. Is that right?

Anyone knows of some way to find out this sequence-table relationship?

Answer 1

The problem is that Oracle allows us to use one sequence to populate columns in several tables. Scenarios where this might be desirable include super-type/sub-type implementations.

You can use the dependencies in the data dictionary to identify relationships. For instance, if you use triggers to assign the values then this query will help you:

select ut.table_name
       , ud.referenced_name as sequence_name
from   user_dependencies ud
       join user_triggers ut on (ut.trigger_name = ud.name)
where ud.type='TRIGGER' 
and ud.referenced_type='SEQUENCE'
/

If you use PL/SQL then you can write something similar for TYPE in ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION'), although you will still require some trawling through the source code to assign tables and sequences when you have multiple hits.

Answer 2

In the database you can search all stored code in your schema like this:

select type, name, line, text
from all_source
where owner = 'MYSCHEMA'
and upper(text) like '%MYSEQ.NEXTVAL%';

In SQL Developer, there is a report to do this.