When you export Oracle data and then import to another environment, sometimes the sequence number gives primary key errors. In this situation, we need to reset Oracle sequences.
This example, we bump up all the sequences by 100.
I have 10 sequences in my schema and I want to increase all of them by 100.
SQL> select sequence_name, last_number from user_sequences;
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
S1 1
S10 1
S2 1
S3 1
S4 1
S5 1
S6 1
S7 1
S8 1
S9 1
10 rows selected.
Currently, their last_number is one because I just created them. In real world, your sequnces' last_number will be different!
Here is a little PL/SQL block that will bump up the sequence numbers by 100.
declare
my_seq_number number;
my_sql varchar2(200);
-- increase the sequence by 100..change it if you want
my_increase_seq_by_number NUMBER := 100;
begin
for my_sequneces in ( select sequence_name from user_sequences
)
loop
my_sql := 'select ' || my_sequneces.sequence_name || '.nextval from dual';
for i in 1..my_increase_seq_by_number
loop
execute immediate my_sql into my_seq_number;
end loop;
end loop;
end;
/
After executing the above PL/SQL block, here is the last_number for all my sequences.
SQL> select sequence_name, last_number from user_sequences;
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
S1 101
S10 101
S2 101
S3 101
S4 101
S5 101
S6 101
S7 101
S8 101
S9 101
10 rows selected.
Hope, this helps.
Please send your feedbacks to poojasoft2011@gmail.com
Thank you.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If you are interested in the book "Oracle SQL Scipting". here is the link.
http://www.amazon.com/Oracle-SQL-Scripting-ebook/dp/B00E2T1DVA/ref=sr_1_1?ie=UTF8&qid=1374436123&sr=8-1&keywords=oracle+sql+scripting
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
