Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Databases
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)