Recent Posts

Pages: 1 2 [3] 4 5 ... 10
21
Oracle News / Oracle OpenWorld Feature: Connecting Customers, Content, and Careers
« Last post by Mike on September 24, 2014, 11:01:35 PM »
Oracle OpenWorld Feature: Connecting Customers, Content, and Careers
24 September 2014, 3:16 pm

Attention technologists! September 28 through October 2 is conference week in San Francisco.

Source: Oracle Magazine - Most Recent

22
Oracle News / Oracle OpenWorld Feature: Connecting Customers, Content, and Careers
« Last post by Mike on September 24, 2014, 11:01:35 PM »
Oracle OpenWorld Feature: Connecting Customers, Content, and Careers
24 September 2014, 3:16 pm

Attention technologists! September 28 through October 2 is conference week in San Francisco.

Source: Oracle Magazine - Most Recent

23
Oracle News / Cover Feature: Faster Answers, Faster Business
« Last post by Mike on September 23, 2014, 05:00:10 PM »
Cover Feature: Faster Answers, Faster Business
23 September 2014, 2:27 pm

It's here! The Oracle Database In-Memory option speeds queries 100 times and transactions 2 times, with no changes to applications.

Source: Oracle Magazine - Most Recent

24
Oracle News / Cover Feature: Faster Answers, Faster Business
« Last post by Mike on September 23, 2014, 05:00:10 PM »
Cover Feature: Faster Answers, Faster Business
23 September 2014, 2:27 pm

It's here! The Oracle Database In-Memory option speeds queries 100 times and transactions 2 times, with no changes to applications.

Source: Oracle Magazine - Most Recent

25
Oracle News / Oracle Magazine September - October 2014 Issue
« Last post by Mike on September 15, 2014, 11:00:42 PM »
Oracle Magazine September - October 2014 Issue
15 September 2014, 11:17 am

Oracle Magazine September/October includes articles on the Oracle Database In-Memory option; conference previews for Oracle OpenWorld and JavaOne, Oracle and the mobile enterprise, KDDI and Oracle, part 2 of our interview with OAUG President Alyssa Johnson; Bob Rhubart on mobile; Mark Rittman on Oracle Big Data Lite Virtual Machine; Arup Nanda  on Oracle Database In-Memory; Steven Feuerstein on persistence; Tom Kyte on implicit conversions; test your database expertise with our Inside OCP column; Partner News; Book Beat and much more

Source: Oracle Magazine - Most Recent

26
Oracle News / Oracle Magazine September - October 2014 Issue
« Last post by Mike on September 15, 2014, 11:00:42 PM »
Oracle Magazine September - October 2014 Issue
15 September 2014, 11:17 am

Oracle Magazine September/October includes articles on the Oracle Database In-Memory option; conference previews for Oracle OpenWorld and JavaOne, Oracle and the mobile enterprise, KDDI and Oracle, part 2 of our interview with OAUG President Alyssa Johnson; Bob Rhubart on mobile; Mark Rittman on Oracle Big Data Lite Virtual Machine; Arup Nanda  on Oracle Database In-Memory; Steven Feuerstein on persistence; Tom Kyte on implicit conversions; test your database expertise with our Inside OCP column; Partner News; Book Beat and much more

Source: Oracle Magazine - Most Recent

27
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.
28
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;
29
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;
30
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

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