Updating sequence in oracle


On the other hand, we can use a trick to change the value of a sequence without recreating it.

Let’s say we have a sequence and its value is 1000, and we want to set it to 500.

One way to do this, perhaps the most typical way, is through parsers that conform to the Simple API for XML (SAX) or the Document Object Model (DOM).

Both of these parsers are provided by Java API for XML Processing (JAXP).

I would just like to know if it is possible to truncate a sequence to reset back to its original starting number? How would dropping the sequence invalidate objects. I guess you would if you run the procedure and the sequence was not there? Let us say I want to reset a sequence dynamically based on the maximum number in a column. Can I use a variable to do the increment like: select max(col) into v_seq from table; column S new_val inc; select seq.nextval S from dual; alter sequence seq increment by -&inc minvalue 0; alter sequence seq increment by v_seq; alter sequence seq increment by 1; 3. If you create the sequence and then run the procedure, it'll successfully recompile itself automagically.

This would be preferred to dropping and recreating the sequence which would invalidate any dependent objects (such as triggers/stored procedures and so on) Tom: 1. If you run the procedure right now, it'll fail to compile.

Errors in file /test01/diag/rdbms/test 01/test 01/trace/ test 01_ora_12966.trc: ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 840186368 bytes disk space from 96636764160 limit ARCH: Error 19809 Creating archive log file to '/test 01/archive/test 01/archivelog/2011_06_10 /o1_mf_1_26619_%u_.arc' Errors in file / test01/diag/rdbms/ test01/ test01/trace/ test01_ora_12966.trc: ORA-16038: log 6 sequence# 26619 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 6 thread 1: '/ test01/oradata/ test01/redo06.log' USER (ospid: 12966): terminating the instance due to error 16038 Instance terminated by USER, pid = 12966 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /test01/archive db_recovery_file_dest_size big integer 91G Here 91G is allocated to db_recovery_file_dest which is at "/test01/archive" location.

If this location is full up to 91G, it will start giving above errors.

This is only possible if you have enough free space on disk to increase the size of db_recovery_file_dest_size.


  1. You get used to them, start to see them as normal people, and learn firsthand that they have flaws and quirks and insecurities like everyone else.

  2. Simon settled in Capharnaum, where he was living with his mother-in-law in his own house (Matthew ; Luke ) at the beginning of Christ's public ministry (about A. Simon pursued in Capharnaum the profitable occupation of fisherman in Lake Genesareth, possessing his own boat (Luke 5:3).

  3. This is the place where all HQ Big Tit Latina Webcam Sex Videos are absolutely arousing and unique!

  4. Some children are on registries because they committed serious sex offenses, such as forcibly raping a much younger child.

  5. At 2.62 bars per 1,000 residents, it barely cracks the top 30.

Comments are closed.