Recent Posts

Pages: 1 ... 6 7 [8] 9 10
71
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;
72
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

73
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

74
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

75
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

76
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

77
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

78
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

79
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

80
Oracle News / PL/SQL: Writing SQL in Oracle Application Express
« Last post by Mike on July 07, 2014, 05:02:35 PM »
PL/SQL: Writing SQL in Oracle Application Express
12 May 2014, 4:37 pm

PL/SQL evangelist Steven Feuerstein delivers Oracle Application Express SQL coding tips on how to minimize the code you write, put it in the right place, and relocate it to packages.

Source: Oracle Magazine - Most Recent

Pages: 1 ... 6 7 [8] 9 10