Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Script to commit every x records

    I am trying to write a script that will insert records from one table (stsc.hist) to another (stsc.hist)temp) and commit every x records. I need to do this because I run into rollback segment issues if I don't. Here's what I have so far but it doesn't work:

    Code:
    DROP TABLE stsc.hist_temp;
    CREATE TABLE stsc.hist_temp AS SELECT * from stsc.hist WHERE ROWNUM < 1;
    
    declare
      i number := 0;
      cursor s1 is SELECT rowid, a.* 
                     FROM stsc.hist a 
                    WHERE a.loc = UPPER('ATL');
    begin
      for c1 in s1 loop
    		  INSERT INTO stsc.hist_temp
                                        WHERE rowid = c1.rowid;
        
          i := i + 1;              
          if i > 10000 then
             commit;
             i := 0;
          end if;
    
      end loop;
      commit;
    end;
    /

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Did you try this?:
    Code:
    DROP TABLE stsc.hist_temp;
    CREATE TABLE stsc.hist_temp NOLOGGING AS 
                   SELECT rowid row_id, a.* 
                     FROM stsc.hist a 
                    WHERE a.loc = UPPER('ATL');


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    How many rows does the table have? this solution will afect performance, I'd think about something else.

    Code:
    DROP TABLE stsc.hist_temp;
    CREATE TABLE stsc.hist_temp AS SELECT * from stsc.hist WHERE ROWNUM < 1;
    
    declare
      i number := 0;
      cursor s1 is SELECT rowid AS id FROM stsc.hist 
                    WHERE loc = UPPER('ATL');
    begin
      for c1 in s1 loop
    		  INSERT INTO stsc.hist_temp
                      select * from stsc.hist     
                      WHERE rowid = c1.id;
        
          i := i + 1;              
          if i > 10000 then
             commit;
             i := 0;
          end if;
    
      end loop;
      commit;
    end;
    /

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    There are probably 2-3 million records. That's why I get the rollback error.

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    hm,
    why don't you play with some database settings? 3M rows is not that much to cause rolback segment issues...

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    How big are your rollback segs. I have inserted many millions of rows without rollback issues. Also try LKBrwn_DBA solution. The nologging will stop most of the redo information from being written.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •