Home » SQL & PL/SQL » SQL & PL/SQL » how to reset all sequences at a time? (oracle, 9iR2, RHEL AS 3)
how to reset all sequences at a time? [message #331401] Thu, 03 July 2008 04:32 Go to next message
sathyguy
Messages: 31
Registered: January 2006
Member
Friends,

for example if i have the x.nextval is 4
alter sequence x increment by -4;
alter sequence x increment by 1;

the above is only for x but i have x,y,z,.......upto 187 sequences...

if i do the below...then i have to increment by same numbers for all.

FOR x IN (SELECT * FROM user_sequences)
LOOP
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || x.sequence_name || ' INCREMENT BY ...';
END LOOP;

we need to create a new user i.e., "user2" in our production db same as "user1" which we are having already. But, without data and the new sequences starting with the "MIN_VALUE" specified in user_sequences

for that i created two users....user1(same as production db) and user2 in our testdb.
i have imported all the tables,procedures,etc...... to user2.
everything is finished except the sequence reset.

now i want to reset all the sequences of user2 to default value.
the problem is some sequences the default value starts from 1 and other will be 101 and some other will be 1001.
so, first i have to know the "MIN_VALUE" and "INCREMENT_BY" for all the sequences from user_sequences.
then i have to reset the sequences according to the above values.
how can i do that?

thanks
Re: how to reset all sequences at a time? [message #331406 is a reply to message #331401] Thu, 03 July 2008 04:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Rerun sequences-part of your initial installation script for the new user, instead of copying the sequences from the existing user.
Re: how to reset all sequences at a time? [message #331410 is a reply to message #331406] Thu, 03 July 2008 04:41 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
sorry, i didnt understand.
please elaborate.

thanks
Re: how to reset all sequences at a time? [message #331411 is a reply to message #331401] Thu, 03 July 2008 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Get sequence definitions with dbms_metadata and create them with the result.

Regards
Michel
Re: how to reset all sequences at a time? [message #331412 is a reply to message #331410] Thu, 03 July 2008 04:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The schema of your original user was created by your installation scripts, right? Rerun the script that creates the sequences on your new user.
Re: how to reset all sequences at a time? [message #331430 is a reply to message #331411] Thu, 03 July 2008 05:29 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
Michel Cadot,
Am i have to drop all the sequences to run the dbms_metadata output?

thanks
Re: how to reset all sequences at a time? [message #331432 is a reply to message #331430] Thu, 03 July 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop? You don't need to drop you create new ones or am I missing something?

Regards
Michel
Re: how to reset all sequences at a time? [message #331438 is a reply to message #331432] Thu, 03 July 2008 05:46 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
then i will clear you....
I have already imported all the table structures,procedures,sequences etc.....from user1 to user2 without data. only few tables are having data's.
now the user2 is having 187 sequences same as user1(because i already imported using imp).
now i want to reset the user2 sequences to their original min and max value as per the user1.
so what should i do?

step1: drop all the sequences in user2.
step2: create the sequences script using dbms_metadata from user1.
step3: run the step2 script in user2.

will it be ok?
Re: how to reset all sequences at a time? [message #331445 is a reply to message #331438] Thu, 03 July 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is OK.
Or you can swap step1 and step2 and do all the stuff with user2.

Regards
Michel

[Updated on: Thu, 03 July 2008 05:55]

Report message to a moderator

Re: how to reset all sequences at a time? [message #331448 is a reply to message #331445] Thu, 03 July 2008 06:04 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
like this.....
login as user2 and do the below...

exec DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.session_transform,'SQLTERMINATOR',true)


spool allseq.sql
select dbms_metadata.get_ddl(object_type,object_name,owner) from dba_objects
where owner='USER2' and object_type='SEQUENCE';
spool off


then run the allseq.sql as user2

am i correct?
Re: how to reset all sequences at a time? [message #331453 is a reply to message #331448] Thu, 03 July 2008 06:26 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like that.
Try it.

Regards
Michel
Previous Topic: REF Cursor Issue
Next Topic: Granting privelege only to execute dbms_aqadm.createqueue
Goto Forum:
  


Current Time: Thu Dec 08 16:44:10 CST 2016

Total time taken to generate the page: 0.08972 seconds