Re: TableSpace / Datafiles resize

"Rath.Yelandur" <Rath.Yelandur_at_Say.No.To.Spam> wrote in message news:<3f0bf877$1_at_shknews01>...
> TableSpace USR_DATA had grown to 10 GB. It has 8 datafiles, each more than
> 2GB.
>
> I have dropped some of the User schemas not in use anymore. Now 3-4
> datafiles are 80% free. How can I resize these datafiles to takeup less
> Hard Disk Space ?
>
> ALTER DATABASE DATAFILE <DBPATH>\USR_DATA04.dbf RESIZE 100M;
> does not work.
>
> How to list the tables/db objects that are allocated in a specific DATAFILE
> ?

Oracle will not let you reduce the datafile below the end of the last object used in the file. So the easiest way to do this (depending on your version and platform, which you should state) is to simply move the objects to a new, smaller datafile.

If you can't do that, you may have to shuffle things about in the datafiles with trickiness to get them in the right file, or guess at resizing smaller and smaller until you hit the error of objects not letting you go further. There are scripts on metalink (see DBA.storage under Database Scripts under Basic Administration) and elsewhere that can tell you where things are in the files.

If you have very old versions of Oracle, there were bugs in the RESIZE that could corrupt the datafile when shrinking.

jg

--
@home.com is bogus.
http://www.govexec.com/dailyfed/0703/070303h1.htm