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

g wayne nichols wrote:
>
> 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

-- 
Bill,

I don't know what version of ORACLE you are using. But if you are using
V7.1 (or later), 
you CANNOT truncate a table with the "DELETE ANY TABLE" privilege.

You need to either own the table or have the "DROP ANY TABLE" privilege!
Incidentally, ORACLE 
has announced a brand new privilege "TRUNCATE ANY TABLE" that might show
up within the year.
You could however create a procedure (owned by SYSTEM) to truncate a
table in any schema, and
work around this problem by granting EXECUTE on this procedure to the
user. This way your DBA 
does not have to grant you "DROP ANY TABLE".


-----------------------------------------------------------------
Naresh Ramamurti 			nramamur_at_worldnet.att.net
-----------------------------------------------------------------