0Let's not get into the very basic things like what is sequence?, What is Cache/Nocache, Cycle/NoCycle etc.
My topic is something very interesting which many of the beginners might not have noticed. My question to you before you read further, let's say we have sequence in one session and its Currval is 10 and you query for the Currval of the same sequence in another session. will you get 10 or something else? Pls note that Sequences retain their values across sessions. i.e they are global in nature, at least till 11g and by default global even in 12c. Before you read further answer this question to yourself and see the truth below.
Let's create a sequence inside the schema A,
CREATE SEQUENCE Test_Seq
MINVALUE 1
START WITH 1
INCREMENT BY 1;
Grant select on Test_Seq to B; ---Another Schema
--Now run this under schema A
Select Test_Seq.nextval from dual; --- You will get 1
Select Test_Seq.Currval from dual; --- So it's 1
-- Now run this under schema B
Select A.Test_Seq.nextval from dual; --- You will get 2
Select A.Test_Seq.Currval from dual; --- You will get 2
--Now under Schema A
Select Test_Seq.Currval from dual; --- What did u answer? It's actually 1, not 2
Select Test_Seq.nextval from dual; --- You will get 3 ofcourse as expected.
--Now under Schema B
Select A.Test_Seq.Currval from dual; --- You will get 2, not 3.
Select A.Test_Seq.Nextval from dual; ---you will ofcourse get 4 as expected
So guys, be aware of this fact while using Currval pseudo column. So, is there a way to find out the Currval without actually incrementing the sequence by calling Nextval?
Simple solution is to check this view- Select * from user_sequences for Schema A
and Select * from All_sequences for Schema B in this example. Last_number column gives you the Sequence value. This works fine with NoCACHE option.
Homework: Recreate the above example of Sequence with CACHE option which is default also and then run the same set of above Currval and NextVal queries and check the above mentioned views.
Hope, you enjoyed it. Take care boys. Bye
