Find if Column 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

There are no direct metadata links between Oracle sequences and any use in the database. You could make an intelligent guess if a column's values are related to a sequence by querying the USER_SEQUENCES metadata and comparing the LAST_NUMBER column to the data for the column.

Answer 3

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.

Answer 4

Thank You Very Much dbbradley!

Managed to find my sequence using your suggestion and it worked but still its not a concrete way of finding the sequence which applies to the table. My fear is what if it actually belongs to another table and I just happen to use it for another table but anyway it looks like there is not straight way to find it.

Thanks anyway.

Cheers!

Ananthi