Q: Failed to extend rollback segment..

From: <rune.slinning_at_bigfoot.com>
Date: Wed, 01 Jul 1998 02:06:38 GMT
Message-ID: <6nc5je$r4e$1@nnrp1.dejanews.com>

Hi All!

We have written a PL/SQL procedure to copy records from one table to another. The tables to copy can be relatively large, with app. 500.000 records.

The procedure is implemented by using a cursor which loads all data from the source table, then we loop through the cursor and inserts record into the destination table. For every 1000 records we do a commit. Before we copy we truncate the table using dynamic SQL.

Shouldn't this commit free up the space used in the rollback segments. For us it doesn't. Can anyone explain why we get ora-01562 and ora-01650 errors.

The procedure log() which is called below, inserts a log statement to a table used for logging. After the insert we do a commit.

~~

procedure copy_equipment_class

---

is
  cursor equip_class_cur is
  select equipment_class,
    description
  from mcp.equipment_class;

  equip_class_rec equip_class_cur%rowtype;

begin

  start_time := sysdate;
  commit_count := 0;

• Truncate table

  table_name := 'cdb.equipment_class';
  cursor_name := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_name, 'truncate table cdb.equipment_class',     dbms_sql.native);
  dbms_sql_rc := dbms_sql.execute(cursor_name);   dbms_sql.close_cursor(cursor_name);

  log(table_name,start_time);

  commit;

• Copy table ...

  for equip_class_rec in equip_class_cur loop     insert into cdb.equipment_class (equipment_class,description)     values (equip_class_rec.equipment_class, equip_class_rec.description);     commit_count := commit_count + 1;
    if commit_count > 1000 then

      commit_count := 0;
      commit;

    end if;
  end loop;
commit;

select count(*) into rows_loaded from cdb.equipment_class;

log(table_name,start_time);

exception
  when others then -- handle all exceptions for this process     log(table_name,start_time);

end copy_equipment_class;

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Jun 30 1998 - 21:06:38 CDT