relationship between sequence and table?

[a128]

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,

[alapps]

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

[clio_usa]

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

[DaPi]

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