Re: How to Alter DUAL table to change value

EscVector wrote:
> Sorry for multiposting, but just thought of something else.
>
> Notice: don't do any of this unless you have a backup.
>
> If you can logon as sysdba and can also shutdown the db, you could try
> the following:
> (modify the schema to work on your system)
> ===========================================================
>
> select 'alter sequence your_schema_here.Inquiries_seq cache ' ||
> ((27349 - your_schema_here.Inquiries_seq.nextval)-2) ||';' "run sql
> below: "from dual;
>
> -- copy/past above statement
> -- (my example generayour_schema_here this: alter sequence
> Inquiries_seq cache 90;)
>
> shutdown abort;
> startup;
>
> select your_schema_here.inquiries_seq.nextval from dual;
>
> shutdown abort;
> startup;
>
> select your_schema_here.inquiries_seq.nextval from dual;
>
> select
> dbms_metadata.get_ddl('SEQUENCE','INQUIRIES_SEQ','your_schema_here')
> from dual;
>
> alter sequence your_schema_here.inquiries_seq nocache;
>
> select
> dbms_metadata.get_ddl('SEQUENCE','INQUIRIES_SEQ','your_schema_here')
> from dual;
>
>
>
>
>
>
>
>
>
> BookerT wrote:
> > I am a novice user, but expected to do "Advanced things"
> >
> > I have inherited some asp/vb code that queries and modifies an oracle
> > database. Some of the code in the database builds a Select Statement
> > from the DUAL table to generate the primary key sequence number for a
> > few tables (maybe just one, I have not determined the full scope yet).
> > One of my tables that I keep trying to add a record to through the asp
> > code keeps giving me a unique constraint error.
> >
> > This happened, I believe because we had to reimport the database from
> > an earlier copy and thus numbers in the old tables reverted back to
> > lower numbers.
> >
> > So for example, since new records have been added, my primary key id
> > number for one table is as high as 27348.
> >
> > however when the code generates a number for the primary key id number,
> > it has a number like 27257. I need to change the DUAL table entry, so
> > that it starts at 27349 and that way when the code goes in and makes a
> > connection to the database, it will pick up 27349 and then sequence
> > from there.
> >
> > How do I do that?
> >
> > Currently in the code it goes:
> > SELECT Inquiries_seq.NextVal AS NextID
> > FROM DUAL
> >
> >
> >
> > Thanks for any opinions.

1. Please do not top-post;
2. what you propose is extremely dangerous. Sequences can be rolled forward way easier without the need for SYSDBA privileges or shutting the db down in unsafe ways:

REM You need to run this in SQL*Plus
acc desired_val prompt "Desired NEXTVAL for your sequence: " set trimspool on termout off heading off feedback off echo off verify off
col currval new_value curr_val noprint
select Inquiries_Seq.Nextval currval from dual; select 'ALTER SEQUENCE Inquiries_Seq INCREMENT BY '|| to_char(&desired_val-1-&curr_val)||' NOCACHE;' from dual;

spool temp.sql
/
spool off
@@temp.sql

REM Remove temporary file, choose one of the two versions below REM If you're on Unix...
host rm temp.sql
REM If you're on Windows...
host del temp.sql

SELECT Inquiries_Seq.nextval from dual;
ALTER SEQUENCE Inquiries_Seq INCREMENT BY 1 CACHE 20;

REM Verify the outcome
set termout on heading on feedback on
SELECT Inquiries_Seq.nextval from dual;

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Thu Dec 14 2006 - 12:13:02 CST