How to use a Commit when we are  deleting the   Large amont of data

nrajasekhar7
nrajasekhar7 used Ask the Experts™
on
Hi

We are using the Oracle 10g database ,We are using the Script to delete the data older than six months, if it fails for one table than everything Rollbacks,
 How to use commit command in between so that after deleting few tables it can commit in between.or can we use the commit command for each each table .
Please suggest asasp!!

Thanks in advance
Raj.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
- you can write a procedure for the delete operations and specify a commit after each table delete.
- or in your script, you can put a commit after each table delete operation eg:

delete tablename1 where ... ;
commit;
delete tablename2 where ... ;
commit;
x77

Commented:
Depending of size of tables and number of rows to delete, this task become complex as you need take care about redo log size, time to delete ...

If the size is aceptable, do one commit only as some table information can be related to other tables.
Include a Whenever sentence to do roolback.

WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}

Database Engineer
Commented:
You need to know the functionnal of application (or at least its LDM) to design the order op delete operation.
Sometimes with foreign key cascading constraints you have to pay attention to manipulated data volume.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Sanjeev LabhDatabase Consultant

Commented:
If you are using proper foreign keys. Then you use the on delete cascade clause so if any dependency it will automatically cater it otherwise you can rollback. Depending upon the average data being deleted you can provide the commit within the iteration likewise.
You can use Truncate which doesn't need a Commit.

Truncate Table1;
Truncate Table2;
etc...

You cannot roll-back from a Truncate.
Naveen KumarProduction Manager / Application Support Manager

Commented:
Add exception handling to your code block which has delete statements and handle the exception/error and do a commit in the exception section.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial