Re: Reclaim my space NOW!

In article <87b4cc$ncf$1_at_nnrp1.deja.com>,   atta707_at_my-deja.com wrote:
> dear all,
>
> please consider this, if you would:
>
> log on as scott/tiger;
> disable primary key for EMP;
>
> loop about 10 to 15 times
> -- incremental inserts...
> insert into EMP select * from EMP;
> end loop
> rollback;
>
> the number of extents allocated for emp grows to 15, about 6 mb of
> tablespace!
>
> now could anyone please help me with how to reclaim this space and get
> the EMP table to shrink back to it's legtimate size or, at least, to
> the size bofore this transaction took place?
>
> i'm reluctant to either drop or truncate this table!!!
>
> thanks in anticipation.
>
> ATTA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

If you don't delete any rows you will likely not be able to reclaim any space. The best way is to issue truncate which will deallocated empty extents and shrink back to initial and next. You could temporarily insert the rows somewhere else, say via create table empback as select * from emp, truncate and then put the rows back via insert into empback select * from emp.

Alternatively if you may be able to alter table xxx deallocate unused; this will only help if you have extents or blocks at the end of the table that are empty. If you have a high, high-water-mark you are likely out of luck.

Pete

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Feb 03 2000 - 11:40:02 CST