Re: How to Alter DUAL table to change value

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.
Received on Thu Dec 14 2006 - 01:05:16 CST