Archive

Archive for January, 2014

get the sequence have created

select * from all_sequences;
select * from user_sequences;
select * from dba_sequences ;

Categories: Oracle Tags: ,

view trigger on table

select trigger_body
from user_triggers
where triggering_event = 'INSERT'
and table_name = 'YOUR_TABLE';
Categories: Oracle Tags: ,

How to Reset a Sequence in Oracle

January 24, 2014 Comments off
create or replace
procedure reset_sequence(p_seq in varchar2)
is
    l_value number;
begin
-- Select the next value of the sequence
    execute immediate
    'select ' || p_seq ||
    '.nextval from dual' INTO l_value;
-- Set a negative increment for the sequence,
-- with value = the current value of the sequence
    execute immediate
    'alter sequence ' || p_seq ||
    ' increment by -' || l_value || ' minvalue 0';
-- Select once from the sequence, to
-- take its current value back to 0
    execute immediate
    'select ' || p_seq ||
    '.nextval from dual' INTO l_value;
-- Set the increment back to 1
    execute immediate
    'alter sequence ' || p_seq ||
    ' increment by 1 minvalue 0';
end;
/
source http://www.oratable.com/reset-sequence-in-oracle/
Categories: Oracle