Solved

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

Posted on 2011-09-05
6
279 Views
Last Modified: 2012-05-12
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
Comment
Question by:nrajasekhar7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 36482688
- 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
 
LVL 15

Expert Comment

by:x77
ID: 36482863
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
 
LVL 7

Accepted Solution

by:
Docteur_Z earned 500 total points
ID: 36485032
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 36491840
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
 
LVL 2

Expert Comment

by:quistlogic
ID: 36493009
You can use Truncate which doesn't need a Commit.

Truncate Table1;
Truncate Table2;
etc...

You cannot roll-back from a Truncate.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 36509394
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question