Re: "Delete From <table>" taking all day

>
> Re: "Delete From <table>" taking all day
>
> From: gwn_at_cyber2.servtech.com (g wayne nichols)
> Reply to: g wayne nichols
> Date: 26 Aug 1996 09:34:06 -0400
> Organization: ServiceTech, Inc.
> Newsgroups:
> comp.databases.oracle
> Followup to: newsgroup(s)
> References:
> Message-ID:
> <4vcbp9$6a3_at_lal.interserv.com>
> <4vnork$ko6_at_sulu.prodata.de>
>
>In <4vnork$ko6_at_sulu.prodata.de> guther_at_privat.prodata.de (Jochen Guther) writes
>:
 

>>meskillw_at_ncr.disa.mil (Bill Meskill) wrote:
 

>>>Hi,
>>>I have a 4000 row table that's taking quite a long
>>>time to delete. What's going on?
 

>>>Bill Meskill
 

>>>FYI. I have my data and indexes separated on lots of
>>>different disks and have allocated 80M for the
>>>SGA (a full database export is 10M). There are only
>>>2 people on the server.
 

>>Have you tried "truncate table" ?
>
>Although this obviously works, it doesn't answer the poster's original question
>.
>Also, you cannot truncate a table you do not own,
>unless you have the "DELETE ANY TABLE" privilege,
>which the DBA's around here don't seem to want the developers to have :-).
>
>Anyway, to answer the question, "What's going on?",
>the command "DELETE FROM <tablename>;" is one transaction;
>therefore Oracle has to load up all that data into a rollback segment,
>so that if your next command is "ROLLBACK WORK", all the deleted rows
>can be automagically restored.
>
>If you cannot "TRUNCATE" the table, write a short PL/SQL block
>with a cursor to read the table and delete each row.
>Then after each group of so many rows (100? 500?), do a COMMIT.
>Note: you may want to re-open the cursor after each commit,
>to avoid -1555 (Rollback segment too small) errors.
>--
>------------------------------------------------------------------------------
>Computer General Rochester, NY (716) 436-6372
> "... providing general computer solutions to specific business problems"
> gwn_at_servtech.com
While it is true that the "Truncate" command can only be done by the table owner unless "delete any object" privilege is granted. There is a stored procedure "Truncate_Table" which gets around this problem. If you would like a copy of it, please drop me an e-mail, and I'll send it right out.

Donald Raikes CPIM
EaglesWing
The Oracle Performance Guru

--
-------------------------------------------------------------------------------
Donald Raikes CPIM
The Oracle Performance Guru
draikes_at_primenet.com  520-579-9481