| how to reset all sequences at a time? [message #331401] |
Thu, 03 July 2008 04:32  |
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 #331438 is a reply to message #331432] |
Thu, 03 July 2008 05:46  |
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 #331448 is a reply to message #331445] |
Thu, 03 July 2008 06:04 |
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?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
