OraFAQ Forum: Server Administration » Deleting from large table

Home » RDBMS Server » Server Administration » Deleting from large table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Deleting from large table [message #369917]

Mon, 06 November 2000 17:40

savitha
Messages: 9
Registered: November 2000

Junior Member

Hi,
I am trying to perform a delete on a large table (3 million rows) I have specified a big rollback segment 1GB. but it always errors out on a different rollback segment why ? and how do I get around this problem.
Thanks
Savitha

Report message to a moderator

Re: Deleting from large table [message #369918 is a reply to message #369917]

Mon, 06 November 2000 17:47

Naveen Kumar V
Messages: 16
Registered: February 2000

Junior Member

Are u trying to delete entire table data?

Report message to a moderator

Re: Deleting from large table [message #369921 is a reply to message #369918]

Tue, 07 November 2000 11:10

Don Thaler
Messages: 8
Registered: October 2000

Junior Member

I would try to break up the delete's into smaller segments. If the table was comprised of part numbers I would delete parts 1-2000,then 2000-4000 etc.

Report message to a moderator

Re: Deleting from large table [message #369928 is a reply to message #369918]

Tue, 07 November 2000 14:17

savitha
Messages: 9
Registered: November 2000

Junior Member

No I am trying to delete based on key columns
I have put this in a cursor...
But the rollbacks get full

Report message to a moderator

Re: Deleting from large table [message #369930 is a reply to message #369918]

Tue, 07 November 2000 15:38

Naveen Kumar V
Messages: 16
Registered: February 2000

Junior Member

are u issuing commit after deleting certain number of rows? If not use commit after deleting certain number of rows.

Report message to a moderator

Re: Deleting from large table [message #369931 is a reply to message #369918]

Tue, 07 November 2000 18:35

savitha
Messages: 9
Registered: November 2000

Junior Member

Yes, I am issing commit after 1000 rows. Is there a way I can select only 1000 rows delete them, then pick the next 1000 etc..

Thanks
Savitha

Report message to a moderator

Re: Deleting from large table [message #369946 is a reply to message #369917]

Thu, 09 November 2000 14:52

jag Anegondi
Messages: 3
Registered: November 2000

Junior Member

Hi savitha

If you are deleting from SQL u have to set the rollback segment before starting the transaction.

SQL> COMMIT; (OR ROLLBACK)
SQL> SET TRANSACTION USE ROLLBACK SEGMENT RBS_BIG;
SQL> UPDATE XX SET BLA-BLA;
SQL>COMMIT;

If it is a PL/SQL program u have to use the dbms_transaction package before issuing the update statement.

If you are commiting after each 1000 rows, then every time after commiting u have to set the rollback segment.

If you have any questions, mail me i can send you sample code.

I hope this helps

Thanks

Jag

Report message to a moderator

Previous Topic:	ORA-06502 error please help
Next Topic:	owa_package in Oracle8i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 20:00:06 CDT 2024