Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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

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.
0
nrajasekhar7
Asked:
nrajasekhar7
1 Solution
 
OP_ZaharinCommented:
- 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;
0
 
x77Commented:
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]}

0
 
Docteur_ZCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Sanjeev LabhDatabase ConsultantCommented:
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.
0
 
quistlogicCommented:
You can use Truncate which doesn't need a Commit.

Truncate Table1;
Truncate Table2;
etc...

You cannot roll-back from a Truncate.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Add exception handling to your code block which has delete statements and handle the exception/error and do a commit in the exception section.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now