Author Topic: How to Reset Sequences in Oracle.  (Read 1106 times)

Prakash

  • Newbie
  • *
  • Posts: 17
    • View Profile
How to Reset Sequences in Oracle.
« on: August 12, 2014, 12:42:31 PM »
This below SQL script will provide with the sequence names that can be reset that are matching to a table name or a column name

Code: [Select]
SELECT   ao.owner, ao.object_name, t1.table_name, t1.column_name
           FROM all_objects ao,
                (SELECT table_name, column_name
                   FROM all_tab_columns
                  WHERE owner = &owner
                    AND data_type = 'NUMBER'
                    AND column_id = 1
                    AND column_name LIKE '%ID'
                    AND table_name IN (SELECT table_name
                                         FROM all_tables
                                        WHERE owner = &owner)) t1
          WHERE CASE
                   WHEN (   INSTR (ao.object_name, 'SQ') != 0
                         OR INSTR (ao.object_name, 'SEQ') != 0
                         OR INSTR (ao.object_name, 'ID') ! = 0
                        )
                   AND (   t1.table_name LIKE
                                 SUBSTR (ao.object_name,
                                         1,
                                         LENGTH (ao.object_name) - 4
                                        )
                              || '%'
                        OR t1.column_name LIKE
                                 SUBSTR (ao.object_name,
                                         1,
                                         LENGTH (ao.object_name) - 4
                                        )
                              || '%'
                       )
                      THEN 1
                   WHEN (   INSTR (ao.object_name, 'SQ') = 0
                         OR INSTR (ao.object_name, 'SEQ') = 0
                         OR INSTR (ao.object_name, 'ID') = 0
                        )
                   AND (   t1.table_name = ao.object_name
                        OR t1.column_name = ao.object_name
                       )
                      THEN 1
                   ELSE 0
                END = 1
            AND ao.owner = &owner
            AND ao.object_type = 'SEQUENCE'
       ORDER BY ao.object_name;

Below script can be used to reset sequences on a specific schema on the database.

DECLARE
   max_val   NUMBER;
   l_value   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema = ' || &owner;

   FOR rec_sequence IN
      (SELECT   ao.owner, ao.object_name, t1.table_name, t1.column_name
           FROM all_objects ao,
                (SELECT table_name, column_name
                   FROM all_tab_columns
                  WHERE owner = &owner
                    AND data_type = 'NUMBER'
                    AND column_id = 1
                    AND column_name LIKE '%ID'
                    AND table_name IN (SELECT table_name
                                         FROM all_tables
                                        WHERE owner = &owner)) t1
          WHERE CASE
                   WHEN (   INSTR (ao.object_name, 'SQ') != 0
                         OR INSTR (ao.object_name, 'SEQ') != 0
                         OR INSTR (ao.object_name, 'ID') ! = 0
                        )
                   AND (   t1.table_name LIKE
                                 SUBSTR (ao.object_name,
                                         1,
                                         LENGTH (ao.object_name) - 4
                                        )
                              || '%'
                        OR t1.column_name LIKE
                                 SUBSTR (ao.object_name,
                                         1,
                                         LENGTH (ao.object_name) - 4
                                        )
                              || '%'
                       )
                      THEN 1
                   WHEN (   INSTR (ao.object_name, 'SQ') = 0
                         OR INSTR (ao.object_name, 'SEQ') = 0
                         OR INSTR (ao.object_name, 'ID') = 0
                        )
                   AND (   t1.table_name = ao.object_name
                        OR t1.column_name = ao.object_name
                       )
                      THEN 1
                   ELSE 0
                END = 1
            AND ao.owner = &owner
            AND ao.object_type = 'SEQUENCE'
       ORDER BY ao.object_name)
   LOOP
      l_value := 0;
      max_val := 0;

      EXECUTE IMMEDIATE    'select max('
                        || rec_sequence.column_name
                        || ') from '
                        || &owner
                        || '.'
                        || rec_sequence.table_name
                   INTO max_val;

      EXECUTE IMMEDIATE    'select '
                        || &owner
                        || '.'
                        || rec_sequence.object_name
                        || '.nextval from dual'
                   INTO l_value;

      IF max_val > l_value
      THEN
         -- Set a negative increment for the sequence, -- with value = the current value of the sequence
         EXECUTE IMMEDIATE    'alter sequence '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || ' increment by -'
                           || l_value
                           || ' minvalue 0';

         -- Select once from the sequence, to -- take its current value back to 0
         EXECUTE IMMEDIATE    'select '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || '.nextval from dual'
                           INTO l_value;

         -- Set the increment back to max val
         EXECUTE IMMEDIATE    'alter sequence '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || ' increment by '
                           || max_val;

         --
         EXECUTE IMMEDIATE    'select '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || '.nextval from dual'
                           INTO l_value;

         -- Set the increment back to 1
         EXECUTE IMMEDIATE    'alter sequence '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || ' increment by 1';

         --
         EXECUTE IMMEDIATE    'select '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || '.nextval from dual'
                           INTO l_value;
      END IF;
   END LOOP;
END;
« Last Edit: August 12, 2014, 01:12:12 PM by Mike »


Mike

  • Administrator
  • Hero Member
  • *****
  • Posts: 2052
    • View Profile
Re: How to Reset Sequences in Oracle.
« Reply #1 on: August 12, 2014, 01:13:22 PM »
Nice script, I'm sure this will be useful to lots of people.

Good stuff. :)


ps- I edited your post to put [ code ] tags around the code.