General
cancel
Showing results for 
Search instead for 
Did you mean: 

get sequence definition in oracle

xzhang9
Occasional Contributor

get sequence definition in oracle

Hi all,

I am using toad with oracle and it is just easy to get the sequence definition viewing the script

CREATE SEQUENCE ABC
START WITH 1048
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

How can we get this definition from sqlplus. I am doing this because I need to dynational run a sql file in sqlplus to recreate it after some process.

Thanks,

Jane
6 REPLIES
Paul Sperry
Honored Contributor

Re: get sequence definition in oracle

select * from dba_sequences where SEQUENCE_NAME='SEQUENCE_NAME_SEQ';
Paul Sperry
Honored Contributor

Re: get sequence definition in oracle

in your case


select * from dba_sequences where SEQUENCE_NAME='ABC_SEQ';
xzhang9
Occasional Contributor

Re: get sequence definition in oracle

Thanks for your answers, that's what I need to generate sql with sql.
Jane
xzhang9
Occasional Contributor

Re: get sequence definition in oracle

Thanks for your answer, that's what I need to generate sql with sql.
Jane
Paul Sperry
Honored Contributor

Re: get sequence definition in oracle

to generate objects withing sql use the select into statement
Yogeeraj_1
Honored Contributor

Re: get sequence definition in oracle

hi Jane,

can you try the following?

select 'create sequence '||sequence_name||' start with '||last_number||' maxvalue '||max_value||' minvalue '||min_value||decode(ORDER_FLAG,'N',' noorder',null)||decode(CYCLE_FLAG,'N',' nocycle',null)||'cache '||cache_size||' increment by '||increment_by||';' from dba_sequences
where sequence_name='ABC'
/

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)