Re: How do you do this w/o a DBA?

In article <387C3799.B283124A_at_home.com>,   kmidkiff <kmidkiff_at_home.com> wrote:
>
> create "purge" tables of parent/child/grandchild table rows to be
deleted.
> this way you can recover in case you delete too much. (create table
> purge_parent as select * from parent where .........) do the same
for each
> child and grandchild table. Once made delete from original tables
using
> primary key column values in purge tables. Always work up from the
bottom.
> So start with the grandchild table, then the child, and last the
parent.
>
> clockwise_at_att.net wrote:
>
> > Hello,
> >
> > I'm in a situation that I need to delete a huge amount of data
without a
> > DBA. Any advice would be appreciated.
> >
> > Suppose you have a table whose associated with 6 other tables shown
as
> > below:
> >
> > tbl_parent(parent_id, parent)
> > tbl_child1(child1_id, child1)
> > tbl_grandchild1(grandchild1_id, child1, grandchild1)
> > tbl_child2(child2_id, child2)
> > tbl_child3(child3_id, child3)
> > tbl_child4(child4_id, child4)
> > tbl_grandchild4(grandchild1_id4, child4, grandchild4)
> >
> > There are about 80000 records in tbl_parent that need to be deleted.
> > There are about 4 times as many records in each child table.
> >
> > Is there any other way to delete all of them other than using
cursor for
> > loops
> > using PL/SQL?
> > The problem I encounter is that when it reaches the memory limit,
oracle
> > will
> > rollback everything.
> >
> > Thanks in advance for any advice.
> >
> > Veronica
> >
>

Why not replace the cursor for loops with simple loops and commit as you go. Commiting as you go will prevent running out of rollback segment space to track all the deletes and will allow you to cancel the job and re-start. There will be less data to process with each run if the delete job impacts the system and you have to limit your runs to low usage periods.

cursor c_logical_name is select rec_to_be_del from ,,,, open c_logical_name;
loop
  fetch ......
  exit when c_logical_name%notfound;
  delete from child where key = parent_key;   commit
end loop;
end;

Actually, you should be able to place commits inside your cursor for loop logic. I would not commit every row but once for every so many, maybe 10 - 50, parent rows.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 12 2000 - 09:20:14 CST