CODEWITHSUNDEEP
sqloracle-databaseddl
mcvkr
mcvkr

Reputation: 3912

How to move a sequence to a new schema?

I want to move a sequence to a new schema preserving the current nextval value, so that when people start to use it there will be no gaps in the sequence, what are the grant and create statements for move?

Note: I do not have SYS or other admin user roles and passwords, I have access to the old and the new schemas only.

Upvotes: 2

Views: 3016

Answers (2)

pOrinG
pOrinG

Reputation: 935

select * from user_sequences where sequence_name = 'sequence_name'

OR

select * from all_sequences where sequence_owner = 'OLD_SCHEMA'  and sequence_name = 'sequence_name'

You can get the last number from there and other parameters as well for the below create statement.

CREATE SEQUENCE #SCHEMA_NAME#.#SEQUENCE_NAME#
  START WITH 21
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER
/

OR if you have a third party tool like TOAD you can just 

desc sequence_name

and it will give you the script from the old schema to create in the new schema.

Upvotes: 0

are
are

Reputation: 2615

the simplest way is get DDL for the sequence in one schema, replace schema name and create it on other schema. 

--prepare sample data
CREATE SEQUENCE my_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

--increase sequence number for test
select my_seq.nextval from dual;
select my_seq.nextval from dual;

--copy the sequence
declare 
  sqltext varchar(512);
  oldschema varchar(64) := 'my_test_schema1';
  newschema varchar(64) := 'my_test_new_schema'; 
begin
  select dbms_metadata.get_ddl('SEQUENCE',upper('my_seq'),upper(oldschema)) into sqltext from dual;
  sqltext := replace(sqltext, '"'||upper(oldschema)||'"', '"'||upper(newschema)||'"');  
  execute immediate sqltext;
end;


--permissions
create any sequence to ...

Upvotes: 3

Related Questions