0

Is there a way to get a resultset from executing a query against an Oracle database that would return a listing of all the tables in the database and which sequence it is using (if any)?

6
  • 4
    You can't. Oracle simply doesn't store that dependency Apr 7 at 12:37
  • 1
    @a_horse_with_no_name, how do you come to this statement? Apr 7 at 13:04
  • 2
    @WernfriedDomscheit: dbfiddle.uk/… Apr 7 at 13:06
  • 1
    @a_horse_with_no_name - strange, why is my solution working then? Well, IDENTITY are not covered as I stated. But typically these sequences are hidden anyway. Apr 7 at 13:09
  • 4
    @WernfriedDomscheit: your solution only detects dependencies between triggers and sequences, not between tables and sequences. My example doesn't use an IDENTITY column, just a default value based on a sequence Apr 7 at 13:12
0

You can query with this one:

SELECT 
    TABLE_OWNER, TABLE_NAME, 
    REFERENCED_OWNER AS SEQUENCE_OWNER, REFERENCED_NAME AS SEQUENCE_NAME
FROM ALL_DEPENDENCIES d
    JOIN ALL_TRIGGERS t ON TRIGGER_NAME = d.NAME AND t.OWNER = d.OWNER
WHERE REFERENCED_TYPE = 'SEQUENCE';

Note, a sequence might be used by more than one trigger. And a sequence can be used also by function, procedure, package, insert, update, etc, which is not covered by this query.

Also this query does not cover IDENTITY columns and sequences, they are hidden.

It depends what you mean by "identify sequences used by tables"?

4
  • 3
    This assumes as well that there is a trigger populating the key. Most of the systems I've been involved with use sequences to generate the keys but don't use triggers. The sequence is specified as part of the insert statement. It may also incorrectly flag dependencies when, for example, a trigger gets the currval of a sequence other than the one it uses for the primary key for some reason. Apr 7 at 13:16
  • 1
    @JustinCave, I would assume, prior 12c SEQUENCE + TRIGGER was the typical method to generate primary keys. Maybe you grew up in different environment than I did. Apr 7 at 13:22
  • 1
    That's not my experience. Certainly there were cases where triggers were used. But less than 50% in my personal sample size. Apr 7 at 13:24
  • @WernfriedDomscheit in my experience the use of triggers to populate ID columns is rare. I certainly wouldn't count on it as a way of linking tables to sequences. Apr 7 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.