RBS runs out of space.

[Sureshy]

Oracle 8.1.7

Hi,

I have a 400Mb table with a 350Mb Index.

I am deleteing 300Mb of data from this table however I kept getting:
ORA-01650: unable to extend rollback segment.

So I create a large (800M) RBS and used set transaction use rollback segment command and tried to delete again. The tablespace this RBS was in had a capacity of 1800M, with 40M initial, 40M next, minextents 20.

Ran again using set transaction use large RBS statement.. however..
got the following message.

ORA-01650: unable to extend rollback segment LARGE_RBS by 5120 in tablespace
RBS2

I am the only user on this system.

I checked DBA_SEGMENTS and noticed the large RBS had grown to 1800M and the delete failed.

Question 1 - why when I am deleteing 300Mb of data and about 275Mb of it's index the operation needs more than 1800M of RBS space to complete the transaction?

Question 2 - How large an RBS do I need to delete this data in one go?
or is there a way around this?

Your assitance will be much appreciated.

Thanks in Advance.

[stmontgo]

awaiting the token ocp remarks.......

[Thomasps]

Any triggers on the table !?

[Sureshy]

been a long time since OCP...

No triggers, in fact this database is merely a data store consisting of just tables and indexes.

No procedures, functions or triggers.

[davey23uk]

instead of dropping why not create a new table as select * from the old one with a where clause to excluse the data you dont want.

Then, drop the old one and rename

[gandolf989]

You could also do something like this:

DECLARE
   TYPE t_my_type IS TABLE OF plan_table.statement_id%TYPE
      INDEX BY BINARY_INTEGER;
   v_mycollection t_my_type;
   v_rowsdeleted  BINARY_INTEGER := 0;
BEGIN
   DBMS_OUTPUT.ENABLE(1000000);

   LOCK TABLE plan_table IN EXCLUSIVE MODE NOWAIT;

   LOOP
	   SELECT statement_id
	     BULK COLLECT INTO v_mycollection
	     FROM plan_table
	    WHERE ROWNUM       < 1000000;
	
	   IF v_mycollection.COUNT > 0
	   THEN
	      FORALL i IN v_mycollection.FIRST..v_mycollection.LAST
	         DELETE plan_table
	          WHERE statement_id = v_mycollection(i);

	      v_rowsdeleted := SQL%ROWCOUNT;

         DBMS_OUTPUT.PUT_LINE(
			   'Records deleted from plan_table: ' || 
            v_rowsdeleted );

         COMMIT;
	   ELSE
	      DBMS_OUTPUT.PUT_LINE('There are no more records to delete!');
	      EXIT;
	   END IF;
   END LOOP

   COMMIT;
END;
/

[cruser3]

Could you post a desc. of the table and the no. of rows as I may have to manage an 8i db. soon - am on 9i and have'nt encountered any RBS problems for a while !!

[Sureshy]

Hi,

Thanks for your responses.

However, I am quite able to get around this problem myself.

I am more looking for an explanation as to why such a large RBS is required to delete this info. In fact a larger RBS than the table and index size put together.

[cruser3]

I could take a guess if you post a desc. of the table and the no. of rows, is the table,index in the same TBS,datafile, OS ?

But besides the triggers etc - are there any other processes taking place when you do this ?