Re: How to Alter DUAL table to change value

On Dec 14, 1:13 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
 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

"Sequences can be rolled
 forward way easier without the need for SYSDBA privileges or shutting  the db down in unsafe ways:"

Of course they can. There is some humor to this post, which is why I put in this second option.

I was offering a creative alternative to the blindly simple.

Dangerous, as it may be, I did test it and it is an option, but I like your option better. :)

Sorry for the top post.

... Received on Thu Dec 14 2006 - 15:54:14 CST