How to Get Sequence DDL from ALL_SEQUENCES

  • by

You don't have to export and import sequences by data pump, you can compose the creation statements by querying ALL_SEQUENCES:

[oracle@primary01 oracle]$ vi compose_create_sequence.sql

set pagesize 0 linesize 255 echo off hea off feedback off trimspool on timing off;
spool create_sequence.sql
select 'set echo on;' from dual;

select 'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name || ' START WITH ' || last_number || ' INCREMENT BY ' || increment_by || ' MAXVALUE ' || max_value || ' MINVALUE ' || min_value || case when cycle_flag = 'Y' then ' CYCLE' else ' NOCYCLE' end || case when order_flag = 'Y' then ' ORDER' else ' NOORDER' end || case when cache_size = 0 then ' NOCACHE' else ' CACHE ' || cache_size end || ';' stmt from all_sequences where sequence_owner in ('ERPAPP', 'FINAPP') order by 1;

select 'exit;' from dual;
spool off;
exit;

As you can see, the only filter I added in the WHERE clause is SEQUENCE_OWNER in the above statement, you can add some other conditions for your statement.

The execution result of the above SQL statement may look like this.

[oracle@primary01 oracle]$ cat create_or_replace_synonym.sql

set echo on;
CREATE SEQUENCE ERPAPP.SQ_PAY_ISSUE_ID START WITH 295109168 INCREMENT BY 1 MAXVALUE 999999999999 MINVALUE 1 CYCLE NOORDER NOCACHE;
...
exit;

Then execute the SQL file in the target database.

Leave a Reply

Your email address will not be published. Required fields are marked *