Recent Posts

Pages: 1 2 3 [4] 5 6 ... 10
31
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;
32
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;
33
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

34
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

35
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

36
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

37
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

38
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

39
Oracle News / DBA: Nonstop Partition Operations
« Last post by Mike on July 07, 2014, 05:02:35 PM »
DBA: Nonstop Partition Operations
7 May 2014, 12:47 pm

Oracle ACE Director Arup Nanda demonstrates how Oracle Partitioning delivers online partition moves, selective indexes on partitions, and asynchronously maintained global indexes to Oracle Database 12c.

Source: Oracle Magazine - Most Recent

40
Oracle News / In the Field: DBA to Big Data DBA
« Last post by Mike on July 07, 2014, 05:02:35 PM »
In the Field: DBA to Big Data DBA
8 May 2014, 2:40 pm

IOUG president offers ideas on how to turn your DBA skills into big data skills.

Source: Oracle Magazine - Most Recent

Pages: 1 2 3 [4] 5 6 ... 10