Find if a column in Oracle has a sequence

Question

I am attempting to figure out if a column in Oracle is populated from a sequence. My impression of how Oracle handles sequencing is that the sequence and column are separate entities and one needs to either manually insert the next sequence value like:

insert into tbl1 values(someseq.nextval, 'test')

or put it into a table trigger. Meaning that it is non-trivial to tell if a column is populated from a sequence. Is that correct? Any ideas about how I might go about figuring out if a column is populated from a sequence?

Answer 1

You are correct; the sequence is separate from the table, and a single sequence can be used to populate any table, and the values in a column in some table may mostly come from a sequence (or set of sequences), except for the values manually generated.

In other words, there is no mandatory connection between a column and a sequence - and therefore no way to discover such a relationship from the schema.

Ultimately, the analysis will be of the source code of all applications that insert or update data in the table. Nothing else is guaranteed. You can reduce the scope of the search if there is a stored procedure that is the only way to make modifications to the table, or if there is a trigger that sets the value, or other such things. But the general solution is the 'non-solution' of 'analyze the source'.

Answer 2

If the sequence is used in a trigger, it is possible to find which tables it populates:

SQL> select t.table_name, d.referenced_name as sequence_name
  2  from   user_triggers t
  3         join user_dependencies d
  4         on d.name = t.trigger_name
  5  where  d.referenced_type = 'SEQUENCE'
  6  and    d.type = 'TRIGGER'
  7  /

TABLE_NAME                     SEQUENCE_NAME
------------------------------ ------------------------------
EMP                            EMPNO_SEQ

SQL>

You can vary this query to find stored procedures, etc that make use of the sequence.