Just in from the Oracle Forum:
Can someone please tell me how to find which sequence is being used by which column in the whole database.
Oracle sequences are not used by Oracle tables, of course, they are used by application code. But if you don’t have the application documentation to tell you which sequences have been used to supply values for which tables, then here are a few clues that might help you work out if there are any relationships.
- Naming: Some sites have naming conventions that might help; so you might find sequence names (in dba_sequences) that look similar to table names. For example, cust_seq and customer_seq look like promising sequences if you have to supply an ID for a customer table. Add probability points if the sequence and table are owned by the same schema.
- Trawl the shared SQL area: if the sequences are being used efficiently, then there will be code in v$sql that does things like “insert into tableX …. sequence_name.nextval …”. Be cautious – it’s a little brutal to hammer your way through v$sql. If you’re using 10g the view v$sqlstats is less latch-intensive to use.
- Try the same trick with dba_source. “select owner, name, type, line from dba_source where upper(text) like ‘%NEXTVAL%’;” you may have to trawl up and down the source for a few lines to find references to the table, of course.
- Coincidences – keep an eye on the high values for ID columns in tables – they’re usually columns with an index that is susceptible to the “index (min/max) range scan” – and check dba_sequences.last_number for any sequence with a very similar value.
If you can think of any more “maybe match” ideas to add to the list, feel free to add them as comments.
Remember, though, the best way to know which sequences have been associated with which table is to make sure that the system was documented properly in the first place. So a time-machine is always helpful.
When it comes to documenting, though, don’t forget that you have the ‘comment’ command, so you could document the connection in the database when you create the table:
SQL> comment on column t1.n1 is 'Generated by sequence XXX'; Comment created. SQL> select * 2 from user_col_comments 3 where comments is not null 4 / TABLE_NAME COLUMN_NAM COMMENTS ------------ ---------- ----------------------------------- T1 N1 Generated by sequence XXX 1 row selected.