Home » SQL & PL/SQL » SQL & PL/SQL » Problem in deleting more than 50 laks of records. . .
Problem in deleting more than 50 laks of records. . . [message #271321] Mon, 01 October 2007 05:10 Go to next message
pateljalpa20
Messages: 3
Registered: September 2007
Location: India
Junior Member
Hello All,

I have a table calles SRVDTL having more than 61 laks of records.
I fired following query:

DELETE FROM SRVDTL WHERE RECNO <= 25810;

But it gave the error of ROLLBACK SEGMENT.
Then I increased the size of Rollback segment RB1 from 121 to 1024. But it still gives same error.

What should I do???
suggest solution. . .

Jalpa.

[mod-edit] removed illiterate IM speak and moved to newbies.

[Updated on: Mon, 01 October 2007 07:57] by Moderator

Report message to a moderator

Re: Problem in deleting more than 50 laks of records. . . [message #271330 is a reply to message #271321] Mon, 01 October 2007 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still increase rollback segment.
Or create a new table with the rows you want to keep, truncate the original table and move back the rows you saved.

Regards
Michel
Re: Problem in deleting more than 50 laks of records. . . [message #271640 is a reply to message #271330] Tue, 02 October 2007 05:09 Go to previous messageGo to next message
pateljalpa20
Messages: 3
Registered: September 2007
Location: India
Junior Member
Thanks Michel,

I did it and worked fine.
But prior I thought, I should increase the size of Rollback Segment upto UNLIMITED and then I could fire my DELETE statement.
Then I should drop Rollback Segment and recreate it.

But U suggested a better solution.

Thanks Again,
Jalpa.
Re: Problem in deleting more than 50 laks of records. . . [message #271645 is a reply to message #271640] Tue, 02 October 2007 05:54 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When the number of rows to delete is far greater than the number you want to keep, the solution with move/truncate/move back is faster and better (it reorganizes the table and its indexes at the same time).

Regards
Michel
Previous Topic: DBMS_LOB.Instr ORA-06502: PL/SQL: numeric or value error
Next Topic: separating the number values with a decimal point
Goto Forum:
  


Current Time: Sun Dec 04 00:16:30 CST 2016

Total time taken to generate the page: 0.09564 seconds