Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!newspeer.monmouth.com!kibo.news.demon.net!news.demon.co.uk!demon!jlcomp.demon.co.uk!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: DROP COLUMN uses rollback? Date: Thu, 6 Jun 2002 22:32:25 +0100 Message-ID: <1023400831.7291.0.nnrp-08.9e984b29@news.demon.co.uk> References: NNTP-Posting-Host: jlcomp.demon.co.uk X-NNTP-Posting-Host: jlcomp.demon.co.uk:158.152.75.41 X-Trace: news.demon.co.uk 1023400831 nnrp-08:7291 NO-IDENT jlcomp.demon.co.uk:158.152.75.41 X-Complaints-To: abuse@demon.net X-Newsreader: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 Lines: 44 Xref: easynews comp.databases.oracle.server:149801 X-Received-Date: Thu, 06 Jun 2002 14:58:31 MST (news.easynews.com) When you go from 'unused' to clearing the columns out, you might want to look at: alter table XXX move nologging; which will recreate the table with the columns present, unavailable, but null - reclaiming most of the space with very little undo and redo. And/or alter table XXX drop unused columns checkpoint 1000; which issues an internal commit every 1000 cleaned rows, and therefore limits the amount of undo used at any instant. (Of course, as a series of small transactions, this may cause other long-running processes to hit a 1555 error). -- Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminars UK June / July Australia July / August http://www.jlcomp.demon.co.uk/seminar.html Stephen B wrote in message ... >That makes perfect sense...thank you! >I'm familiar with the UNUSED option, but I'll need to free up the space >eventually...this particular database is in development >and will ultimately be deployed accross the country...unfortunately space is >a consideration in deployment. > >Thank you especially Howard, for taking the time to clearly delineate why >this particular type of DDL requires rollback. >