Sequences ARE supported over a database link.
You put the link in after the sequence name, not the table name.
EG
select seqowner.seq_name.nextval@
Don't know about the performance impact, but with performance, it's a mtter of
1) What are my requirements
2) What alternatives do I have for meeting them
3) Which is the best performing of the alternatives.
If you NEED a sequence to be shared between databases, then the performance is something you cope with.
You may be able to use separate number ranges for databases, as the previous poster suggested.
Main Topics
Browse All Topics
by: jdcowellPosted on 2002-12-02 at 08:28:55ID: 7520771
I'm assuming you want to access a sequence on a remote database using a database link?
There would be a performance hit if it could be done, but sequences are not supported over database links (certainly not in 817)
If tou try a
CREATE SEQUENCE TEST;
SELECT TEST.NEXTVAL FROM DUAL;
This should return 1.
Now connect to another database which has a DB link
to the database with the TEST sequence on it and try a
SELECT TEST.NEXTVAL FROM DUAL@yourdblink;
You will get an error saying the sequence does not exist because Oracle will access the sequence on the local database, not the remote one. Oracle does validate the db link, but does not use it.
A possible option for you is to have the same sequence name on different databases but use a different range of
values on each database.
Hope this helps...