Updating sequence in oracle

If they are not specified, the default and are both 1.Assume we have a table with the following structure: Table USER_TABLE It is worth noting that a sequence is independent of a table.You can also query the sequence for new values before submitting new rows.That means your code can recognize the key values for new rows before you insert them into the database.Investigating the original report problem required an AWR analysis and a SQL trace (actually a 10046 level 12 trace – tracing the bind variables was of critical importance in troubleshooting the initial problem with the report).SELECTing a sequence value using the NEXTVAL function is supposed to be a fairly lightweight process.Problem is, every time the database is redeployed, the next value in the sequence is incorrect because it has to be set statically.So I found this sql on the internet which will update your sequence number according to the next unused ID from your table.

I run it with this command: sqlplus user/[email protected]/path/scriptname My modified sql. UPDATE: Thanks to Dan Wilson, Business Analyst where I am currently under contract, for giving me some sql to handle this much better than the sql I had previously found.I modified the sql because my sequence spanned more than one table.So the modified sql uses a temp table to find the next unused id across multiple tables.While the reasons for this design decision can only be guessed at, the good news is that even for users on older Oracle systems, there is a possible workaround to circumnavigate this pitfall and create your own auto incremented primary key column.The first step is to create a in your database, which is a data object that multiple users can access to automatically generate incremented values.

Search for updating sequence in oracle:

updating sequence in oracle-83updating sequence in oracle-7updating sequence in oracle-78updating sequence in oracle-82

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating sequence in oracle”