Recent Posts

Pages: [1] 2 3 ... 10
1
PSOUG / Re: How to Reset Sequences in Oracle.
« Last post by Mike 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.
2
PSOUG / How to Reset Sequence in Oracle (Single Sequence)
« Last post by Prakash on August 12, 2014, 01:08:47 PM »
-- Get the max value of the column where the sequence is being used, max_value
SELECT MAX (column_name) + 1
  FROM table_name;

-- Get the current value of the sequence, l_value
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to the current value of the sequence
ALTER SEQUENCE sequence_name INCREMENT BY -l_value MINVALUE 0;

-- To make the above DDL effective, execute the below command and make the sequence value to 0
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to the desired value
ALTER    SEQUENCE sequence_name INCREMENT BY max_value;

-- To make the above DDL effective, execute the below command and make the sequence value to max value of actual column
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to increment by 1 for regular use of the sequence
ALTER  SEQUENCE sequence_name INCREMENT BY 1;

-- To make the above DDL effective, execute the below command and test the sequence that is altered
SELECT sequence_name.NEXTVAL
  FROM DUAL;
3
PSOUG / How to Reset Sequences in Oracle.
« Last post by Prakash 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;
4
Oracle News / Oracle ADF: Region Extreme: Multi-Task-Flow Binding
« Last post by Mike on July 22, 2014, 11:03:48 AM »
Oracle ADF: Region Extreme: Multi-Task-Flow Binding
17 July 2014, 12:14 pm

Render an unknown number of task flows in page or dashboard regions.

Source: Oracle Magazine - Most Recent

5
Oracle News / Analyst: Deploy Applications in Different Environments
« Last post by Mike on July 22, 2014, 11:03:47 AM »
Analyst: Deploy Applications in Different Environments
21 July 2014, 3:34 pm

Organizations look to today's application server platforms to run high-performance enterprise applications and services.

Source: Oracle Magazine - Most Recent

6
Oracle News / Peer-to-Peer: Close Encounters
« Last post by Mike on July 16, 2014, 11:00:48 PM »
Peer-to-Peer: Close Encounters
16 July 2014, 12:26 pm

Three peers extol the virtues of in-person classes and conferences.

Source: Oracle Magazine - Most Recent

7
Oracle Security Alerts / Oracle Critical Patch Update Advisory - July 2014
« Last post by Mike on July 15, 2014, 05:00:25 PM »
Oracle Critical Patch Update Advisory - July 2014
15 July 2014, 1:30 pm

Oracle Critical Patch Update Advisory - July 2014

Source: Oracle Security Alerts

8
Oracle News / In the Field: Learning Circle, Part 1
« Last post by Mike on July 14, 2014, 11:01:44 PM »
In the Field: Learning Circle, Part 1
14 July 2014, 3:50 pm

President Alyssa Johnson talks about OAUG's evolving mission and commitment to innovation through community.

Source: Oracle Magazine - Most Recent

9
Oracle News / PL/SQL: The Joy of Low- Hanging Fruit
« Last post by Mike on July 10, 2014, 05:05:39 PM »
PL/SQL: The Joy of Low- Hanging Fruit
9 July 2014, 7:00 am

Steven Feuerstein wants to know: Are you using BULK COLLECT and FORALL for bulk processing yet?

Source: Oracle Magazine - Most Recent

10
Oracle News / Architect: Building Bridges
« Last post by Mike on July 10, 2014, 05:05:39 PM »
Architect: Building Bridges
10 July 2014, 7:14 am

Organizational silos thwarting IT architecture goals? Put away the sledgehammer.

Source: Oracle Magazine - Most Recent

Pages: [1] 2 3 ... 10