{"id":359,"date":"2017-06-15T04:52:10","date_gmt":"2017-06-15T08:52:10","guid":{"rendered":"http:\/\/dbtricks.com\/?p=359"},"modified":"2017-06-15T04:52:10","modified_gmt":"2017-06-15T08:52:10","slug":"short-and-sweet-way-to-increase-sequence-value-to-be-higher-than-the-max-value-in-the-table","status":"publish","type":"post","link":"https:\/\/dbtricks.com\/?p=359","title":{"rendered":"Short and sweet way to increase Sequence value to be higher than the max value in the table"},"content":{"rendered":"<p>Many times during the development process, there is a need to copy \/export\/backup table data between environments or stages.  While it is easy to export the data using SQL inserts, it is also very easy to forget the sequences related to the tables.<br \/>\nSometimes, this practice results in <strong>DUP_VAL_ON_INDEX exception  (ORA-00001)  <\/strong>when you use NEXTVAL in your insert. The tables may already include high values but the sequence is lower than the max values in the table.<br \/>\nTherefore it is important to remember to increment the sequences to a value higher than the max value in the table.<br \/>\n<br \/>\nThe most common way to increase the sequence value to the next value in the table is to:<br \/>\n<br \/>\n\t1)\talter the sequence increment to the difference between the current value of the sequence and the max value in the table.<\/p>\n<pre lang=\"sql\">\r\nALTER SEQUENCE sequence-name INCREMENT BY 500;\r\n<\/pre>\n<p>2)\tIssue a dummy nextval request<\/p>\n<pre lang=\"sql\">\r\nselect sequence-name.nextval from dual;\r\n<\/pre>\n<p>3)\tAlter the sequence increment value back to the original increment  <\/p>\n<pre lang=\"sql\">\r\nALTER SEQUENCE sequence-name INCREMENT BY 1;\r\n<\/pre>\n<p>\nAnother option of course is to drop the sequence and recreate it with the required value but this may invalidate objects referencing to it. Also, it will force you to re-apply all grants and permissions.<br \/>\nBoth above methods will work but not everyone have permissions to change the sequence. In addition, DDL operations are always risky.  So, If you are looking for a short and sweet (or quick and dirty) way to increase the sequence value, look no further.<br \/>\n<\/p>\n<pre lang=\"sql\">\r\nselect  level, sequence-name.NEXTVAL\r\nfrom  dual \r\nconnect by level <= (select max(column-using-the-sequence  ) from table-name);\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Many times during the development process, there is a need to copy \/export\/backup table data between environments or stages. While it is easy to export the data using SQL inserts, it is also very easy to forget the sequences related to the tables. Sometimes, this practice results in DUP_VAL_ON_INDEX exception (ORA-00001) when you use NEXTVAL [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-359","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/359","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=359"}],"version-history":[{"count":5,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/359\/revisions"}],"predecessor-version":[{"id":364,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/359\/revisions\/364"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}