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?

Oracle doesn't track it because there is no such relationship, except by convention. This is why you usually use some kind of naming convention, e.g. table XYZ has sequence SEQ_XYZ. — Jeffrey Kemp
– Jeffrey Kemp, Commented Mar 3, 2010 at 4:08

APC · Accepted Answer · 2010-03-02 13:19:07Z

9

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.

answered Mar 2, 2010 at 13:19

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nanosoft Over a year ago

If you have sequence_name and you want to check for which tables it is being used via triggers you can use above query with one more "and" condition specifying name of sequence : 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' and ud.referenced_name = 'YOUR_SEQ_NAME';

Tony Andrews · Accepted Answer · 2010-03-02 13:18:30Z

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.

answered Mar 2, 2010 at 13:18

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

2 Comments

jva Over a year ago

This will not take care of cases like "SELECT MYSEQ . NEXTVAL FROM dual;"

Tony Andrews Over a year ago

True, and that is always an issue with text searches. You could use '%MYSEQ%.%NEXTVAL%', but then that might produce false positives for something like 'MYSEQ2.NEXTVAL'. No easy answers for that!

Quassnoi · Accepted Answer · 2010-03-02 13:07:28Z

2

If your sequence is used in a trigger, the trigger will be listed in the sequence's "referenced by" list.

If your sequence is only used in the source-code queries, than yes, browsing the code is the only way.

answered Mar 2, 2010 at 13:07

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

1 Comment

mcrisc Over a year ago

If stored procedure sp_something references sequence seq_something, then sp_something will surely appear in seq_something's "referenced by" list?

Martin Milan · Accepted Answer · 2010-03-02 13:08:08Z

1

Use GREP to scan your entire source for "myseq.NextVal" - myseq being the one you're looking for....

answered Mar 2, 2010 at 13:08

Martin Milan

6,4002 gold badges36 silver badges44 bronze badges

3 Comments

mcrisc Over a year ago

That has been being my approach. grep -Prine "myseq\." . on the entire code base. Trouble is, people doesn't have all code under version control. I think there are lots of code (procedures) stored only on the database.

Guru Over a year ago

If you can refer only the stored code in DB, then try ALL_SOURCE. If you are in 10g, use REGEXP_LIKE function to search.

mcrisc Over a year ago

@Guru it's 9i. But anyways, it's good to know about REGEX_LIKE. Thanks.

Collectives™ on Stack Overflow

Which Oracle table uses a sequence?

4 Answers 4

1 Comment

2 Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related