Thank you, Mark Based on the oracle's recomendation, about 1 G will be kept in the table and about 40G+ will be released. Does that this mean that the max undo I will need is 1G? Thank you Eugene Gurevich "Mark W. Farnham" <mwf@xxxxxxxx> To 12/07/2009 11:29 <ahmusch@xxxxxxxxx>, AM <genegurevich@xxxxxxxxxxxx> cc <oracle-l@xxxxxxxxxxxxx> Subject RE: table shrink For a quick rough estimate, remember that the only undo you'll get from the empty blocks is a tiny amount from the space management. It is also very unlikely that any block lower in extent order than the first empty block can move. Finally, the total of blocks that still do have contents is a rough ceiling on the undo, so if you're keeping less than you're shrinking that is a consideration. If you can be out of action a bit, create table as select to keep the rows you want to retain is probably the cheapest thing unless the amount kept is greater than two times (and then measurements are required and the results vary depending on indexes, etc.) the amount to be shuffled, and you might have an opportunity to order the select so that in your destination your most frequently used access path gets the best possible cluster score and lowest cost for both the CBO and actual run time. Then of course you probably do a rename and re-index. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Adam Musch Sent: Monday, December 07, 2009 10:58 AM To: genegurevich@xxxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: table shrink Remember, if it's moving rows (and therefore, changing ROWIDs), it's also got indexes to update. So it could require significantly more UNDO than the size of the table. If you can afford the downtime, you may be better off with impdp/expdp, alter table move, and/or online table redefinition. On Mon, Dec 7, 2009 at 9:46 AM, <genegurevich@xxxxxxxxxxxx> wrote: > Hello everyone: > > I am researching alter table shrink space command in 10.2.0.3 before I try > it in production. According to oracle adviser, there is a table that can be > shrank > to release over 40G of space and another which can release over 100G. I > plan to execute the commands during a maintenance window to minimize the > usage of these tables. I have run a small test in dev and saw that these > command have been using undo tablespace. That tablespace is only 20G > in my database. Does it need to match the size of the 'freeable' disk > space, in other word do I need to increase it to about 100G? I also was > told that > i can cancel this command any time (in case there is a performance > degradation) and whatever disk space has been released will stay released > and > I can run the command again to release the rest of the disk space. I have > not been able to find this in any oracle documentation however. I don't > have any table in dev to test a large shrink command. > > If anyone had any experience with this command, can you please address my > questions on undo TS and cancelling the command please. I am > alsowondering whether there is any downside to this operation. > > Thank you > > Eugene Gurevich > > > Please consider the environment before printing this email. > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Adam Musch ahmusch@xxxxxxxxx -- //www.freelists.org/webpage/oracle-l Please consider the environment before printing this email. -- //www.freelists.org/webpage/oracle-l