relationship between sequence and table?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: relationship between sequence and table?

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    relationship between sequence and table?

    Is there any relationship between a sequence and a table? If I need to copy some tables to another database and I know some where in our Java code inserts data into the tables usinig sequences, how can I find out what sequences related to the copied tables need to be recreated in the destination database?

    I could not find a data dictionary view to find sequences related to the a specific set of tables, USER_SEQUENCES does not contain any table information.

    Thanks,

  2. #2
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    This is a prime example of why naming conventions are so important.

    Since sequences are are used to get a unique number (Primary Key), the "Standard" name for a sequence is:

    TABLE_SEQ

    i.e. (To use the SCOTT schema as an example)

    The EMP table would have the sequence for the employee number(id) of EMP_SEQ.

    1. Use the view dba_sequences to get all your sequences
    2. Scan your existing Java code for these sequence names
    3. Take the results and view the immediate surrounding code to see what tables are used.

    This should get your answers..

    P.S. This could have been avoided by the "Anal" DBA with naming standards/conventions
    alapps

    Fast, Cheap, Reliable... Pick Two(2)

  3. #3
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Thumbs up

    A standard practice is to name the sequences after the table name. eg - TABLENAME_SEQ. This way a query like:

    SELECT sequence_name FROM user_sequences WHERE sequence_name LIKE 'TABLENAME%';

    will give you an asnwer. Otherwise grep trough your java code for TABLENAME and INSERT strings.


    Hope that helps,

    clio_usa - OCP 8/8i/9i DBA

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: relationship between sequence and table?

    Originally posted by a128
    Is there any relationship between a sequence and a table?
    NO! Which is a good thing, coz we stamp ALL the rows in ALL our tables with a sequence number from one SEQUENCE (as well as the ususal stuff for PK's) - great help for debugging & auditing.

    As the others have said the answer for your question can only come from the code or the documentation
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width