1

Kind of a general question here but is there an easy way to determine, in Oracle database, if a field has a sequence number attached to it? This seems like it should be obvious, but I'm missing it.

Thanks.

alexherm
  • 1,362
  • 2
  • 18
  • 31

2 Answers2

4

In general, no. Sequences are separate first-class objects. Normally, you'd create one sequence per table and use that sequence consistently to populate the key (via a trigger or via whatever procedural API you have to do the insert). But nothing stops you from using the same sequence to populate multiple tables or writing code that doesn't use the sequence when one exists.

If you are on a recent version of Oracle and you are looking only at columns that are explicitly created as identity columns rather than the old-school approach of creating a separate sequence and using a trigger/ column default to populate the key, you can use the identity_column column in all_tab_columns (or user_tab_columns/ dba_tab_columns) to see whether the column was declared as an identity.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you. Yes, I am less than old school and was familiar with using IDENTITY (in SQL Server at least) far before I knew that sequences existed. – alexherm Aug 29 '19 at 23:05
1

there is no way to attach a sequence to a field in oracle, what you can do is to use the sequence in your application as you see fit.

General you'll need to look for triggers on the table, and for procedures that maybe used to insert data to this table, some people use those to regulate sequence use and to sort of attach it to a field but it's not a real attachment but they are just using the sequence and it could be used in many other ways.

alibttb
  • 526
  • 4
  • 19