APEX-AT-WORK no image

Migrate Sequences

Von Tobias Arnhold 3.22.2013
During one of my projects I had an issue when I copied the DDL from my test environment into my productive system. Unfortunately I needed some of the test data in the prod system as well. For that I had to migrate most of the sequences starting with their last number. SQL Developer created those sequences starting with 1. This simple code fixed my issue.
select 'DROP SEQUENCE "'||SEQUENCE_NAME||'";' || 
       ' CREATE SEQUENCE "'||SEQUENCE_NAME||'"' ||
       ' MINVALUE 1 MAXVALUE 999999999999999999999999999' ||
       ' INCREMENT BY 1 START WITH ' || to_char(last_number+1) ||
       ' NOCACHE NOORDER NOCYCLE ; ' as seq_code
from all_sequences
where sequence_owner = '#SCHEMA_NAME#';
Cheers Tobias

Post Tags:

1 One Comment " Migrate Sequences "

Sokrates 22 March, 2013 13:59

wouldn't be

select
'drop sequence "' || sequence_owner || '"."' || sequence_name || '";'
|| chr(10)
|| dbms_metadata.get_ddl('SEQUENCE', sequence_name, sequence_owner) || ';'
as seq_code
from all_sequences
where sequence_owner='#SCHEMA_NAME#';

a bit more accurate ?