Packing or freeing datafiles?

[Guest]

We have a tablespace that is now much bigger than needed to
hold the data in it, and I would like to shrink it.
However, all datafiles have some extents allocated near the end of the
file,
so cannot just downsize them.
Is it possible to first move all allocated extents away from one
datafile to another,
or to "pack" a datafile, i.e. move all extents to the beginning of the
file.
I only have access thru TOAD, no OEM, so would need the SQL-commands
(Oracle 9i).
Any help appreciated.

[Steve Howard]

Look up "alter table move" and "alter index rebuild" in the doco.


Regards,


Steve

[Guest]

Hi Steve,

Yes, I know about those...
that you can tell in what tablespace to rebuild the table.

But it doesnt really give me control of in what DATAFILE
or at what positions within a file to use for the rebuild,
does it?


stephen.how...@us.pwcglobal.com wrote: Look up "alter table move" and "alter index rebuild" in the doco. Regards, Steve

[Access]

<grz01@spray.se> wrote in message
news:1115225892.974577.199860@g14g2000cwa.googlegroups.com... Hi Steve, Yes, I know about those... that you can tell in what tablespace to rebuild the table. But it doesnt really give me control of in what DATAFILE or at what positions within a file to use for the rebuild, does it?

No. What you could is : create a new tablespace, do ALTER TABLE MOVE to move
the tables to the new tablespace, then drop the old tablespace.

stephen.how...@us.pwcglobal.com wrote: Look up "alter table move" and "alter index rebuild" in the doco. Regards, Steve

[Yong Huang]

Other than moving it to a new tablespace (as Matthias says), run "alter
table move" once. Assuming your tablespace is LMT, the table blocks
should migrate toward the beginning of the file. (Ref. Jonathan Lewis,
"Practical Oracle8i", pp.153-4)

It seems that when you do it again, or more correctly, even number of
times, the blocks migrate back.

Yong Huang