Home » SQL & PL/SQL » SQL & PL/SQL » Need help to purge data from a table
Need help to purge data from a table [message #269798] Mon, 24 September 2007 12:39 Go to next message
comson
Messages: 5
Registered: September 2007
Location: Mississauga
Junior Member
Hi all,

My requirement is to purge old transaction data into a different schema. We're using Oracle9i Enterprise Edition Release 9.2.0.5.0. All sales, purchase order and other transactions are logged into txnheader table. Using Id and Receipt# column values of txnheader table, 1 or many records are entered into txndetail, txnserial, txntender, commission and inventorycountheader tables.

The approach I used was - collect old data from txnheader table into a cursor on a monthly basis. Then I do the cursor processing. For each record in the txnheader cursor, I insert it into the purge table in different schema. Then I pass the Id and Receipt# of that record to another procedure. In that procedure, I collect records from txndetail, txnserial, txntender, commission and inventorycountheader tables in individual cursors. I open each cursor, insert them into corresponding purge table in different schema and delete the record(s) from the original table. Finally, I delete the txnheader record from the original table. My approach didn't succeed because I encountered undo table space error even after allocating more space to UNDO_TABLESPACE.

On an average, the txnheader table has 160,000 records per month. Each record will have 1 or many records in txndetail, txnserial, txntender, commission and inventorycountheader tables.
So, the total record count would come to approximately 800,000.
In my approach I am trying to do 800,000 insertions and 800,000 deletions without any commit in-between. I didn't use commit fearing data loss if the batch process fails in the middle. I couldn't’t identify a logical point where I can do a commit.

Is there any other way that I can purge data based on my requirement? Kindly suggest me a solution to accomplish my task.

Thanks and Regards,
Arun

[Updated on: Mon, 24 September 2007 12:39]

Report message to a moderator

Re: Need help to purge data from a table [message #269800 is a reply to message #269798] Mon, 24 September 2007 12:46 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Instead of using cursor for inserting detail records why are you not using
INSERT INTO <TABLE_NAME> (SELECT * FROM .... WHERE ID=...)
and then delete it same way using where condition. It will make the process faster and easier and will consume less undo space.

For your safety you can define SAVEPOINT after some transaction. if perticular process fails then you can easily rollback to that savepoint defined.


Cheers
Soumen
Re: Need help to purge data from a table [message #269801 is a reply to message #269798] Mon, 24 September 2007 12:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>My approach didn't succeed because I encountered undo table space error even after allocating more space to UNDO_TABLESPACE

Without knowing the specific reason for the failure, I won't speculate on any possible solution(s).

[Updated on: Mon, 24 September 2007 12:47] by Moderator

Report message to a moderator

Re: Need help to purge data from a table [message #269804 is a reply to message #269798] Mon, 24 September 2007 13:00 Go to previous messageGo to next message
comson
Messages: 5
Registered: September 2007
Location: Mississauga
Junior Member
Soumen, I shall try that.we can use SAVEPOINT, but will I be able to find out which recors were purged successfully and which failed?

Anacedent, I have uploaded the error message for your reference./fa/3103/0/
  • Attachment: Error.jpg
    (Size: 48.16KB, Downloaded 224 times)

[Updated on: Mon, 24 September 2007 13:01]

Report message to a moderator

Re: Need help to purge data from a table [message #269808 is a reply to message #269798] Mon, 24 September 2007 13:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What is the size of the UNDOTBS1 tablespace datafile(s)?
How much free disk space is available to increase the size of this tablespace?
Re: Need help to purge data from a table [message #270029 is a reply to message #269801] Tue, 25 September 2007 08:25 Go to previous message
comson
Messages: 5
Registered: September 2007
Location: Mississauga
Junior Member
The size of the UNDOTBS1 tablespace datafile is 6144 MB.
Approximately 12 GB is available.


Previous Topic: Cursor? Please guide!
Next Topic: NUMBER column
Goto Forum:
  


Current Time: Sat Dec 10 01:37:46 CST 2016

Total time taken to generate the page: 0.08709 seconds