This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,831 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Information about Sequences used by tables

SVM 1225
SVM 1225 Member Posts: 114
edited Dec 7, 2016 1:17AM in Database Ideas - Ideas

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.

SVM 1225ctrieb
7 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown
    edited Dec 7, 2016 10:00AM

    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.

    SVM 1225berx
  • GregV
    GregV FranceMember Posts: 3,118 Gold Crown

    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
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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
    ctrieb Dipl. - Inf.(FH) WormsMember Posts: 314 Gold Trophy

    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
    BluShadow Mr UKMember, Moderator Posts: 42,706 Red Diamond

    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).

    William Robertson