Information about Sequences used by tables

SVM 1225

Sequence can be used to populate the column data of a table. If someone wants to find out if any table is using any sequence then there is no direct way through which I can get the required details. Like we are able to check the dependent objects but not sequence related information. If such information can be saved into any data dictionary views then it will be very helpful.

Sven W.

If client code makes the connection between the sequence object and the table object, then there is no direct way to find this out.

However if you use the new 12c functionality GENERATED AS IDENTITY then you will not need to know the name of the sequence.

For DEFAULT VALUE columns that use a seqeunce to populate the column, the name of the sequence is in the DATA_DEFAULT (long) column in the USER_TAB_COLUMNS view.

So downvoted, because (depending on the environment) the suggested functionality is already there or doesn't make sense.

GregV

Hi,

There is no direct way because a sequence and a table are two independant objects. You can very well use a sequence just for calculation purpose. It's only from12c that you can assign the NEXTVAL and CURRVAL pseudocolumns directly as default values for a table's columns.

BPeaslandDBA

As @GregV said, these are independent objects. The best way to get what you want is to search your application's Source Control to figure out which code sections are using this sequence for this table.

Cheers,
Brian

ctrieb

Yes, both types are independent objects, but when you make the decision to populate a column with a sequence, it makes sense you have this documented in the Data Dictionary, as an optional info. I voted up,

BluShadow

Yes, both types are independent objects, but when you make the decision to populate a column with a sequence, it makes sense you have this documented in the Data Dictionary, as an optional info. I voted up,

ctrieb wrote:Yes, both types are independent objects, but when you make the decision to populate a column with a sequence, it makes sense you have this documented in the Data Dictionary, as an optional info. I voted up,

That doesn't make sense.

Just because a sequence was used once to provide a value for a column on a table, doesn't mean that the table is dependent on it.  It's possible that a sequence may have been used to create some data once, but after that it's populated through some programmatic means instead.

The fact is that sequences are independent, so by their very nature do not hold a dependency (in either direction).