UrbanPro
true

Learn Oracle Database from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

Interesting thing about Sequences in Oracle

Gavi Y.
19/12/2016 0 0

Let'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

0 Dislike
Follow 0

Please Enter a comment

Submit

Other Lessons for You

Try too choose suitable industry which will give more happyness
In any industry, if its suitable and if its more interest then every body can create wonders. So let choose which is best suitable to you mean while dont think u want to become no 1. As per your capabilities,...
K

K C Reddy

0 0
0

To find duplicate records in a table (SQL Server)
Suppose the given table has 2 columns (id, phone_number): SELECT id, COUNT(id) FROM table_nameGROUP BY id HAVING COUNT(id)>1
A

Arti Gupta

0 0
0

How To Prepare For OCA Exam?
Oracle Certified Associate certification programs are very costly to take the exam. They really deserve it because you get a high value of this certification in the job market. This Article is meant for...

Data Warehousing - End to End
Data warehouse - An Idea Subject Oriented - A data warehouse is subject oriented because it provides information around a subject rather than the organization's ongoing operations. These subjects can...

Datafile
Overview of Data files A table space in an Oracle database consists of one or more physical datafiles. A datafile can be associated with only one tablespace and only one database.At least one datafile...
R

Rajasekharreddy K.

0 0
0
X

Looking for Oracle Database Classes?

The best tutors for Oracle Database Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn Oracle Database with the Best Tutors

The best Tutors for Oracle Database Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more