Re: How to free unused space in a tablespace?

Brian Peasland wrote:
> Odd Bjørn Andersen wrote:
> > I am new to Oracle so this may seem a silly question, but is there a simple
> > way to free unused space in a tablespace. I could export and import the data
> > I suppose, but the problem is that there is not much free disk space.
> >
> > Regards
> > Odd B Andersen
> >
> >
>
> Unused space is already free to use. It can be seen in the
> DBA_FREE_SPACE dictionary view.
>
> Do you mean to coalesce free space? Which version of Oracle are you
> using? Are you using Locally Managed Tablespaces or Dictionary Managed?
>
>
> Cheers,
> Brian
>

The OP mentions freeing unused space within a tablespace and the fact that the disk file system does not have a lot of room so I believe that the OP wants to know how to reduce the size of a tablespace giving the space back to the OS.

Look up information related to the command ALTER DATABASE DATAFILE RESIZE in the SQL and DBA Administration manuals.

To effectively release space from a datafile you need to move all the extents for the objects allocated in the database from whereever their extents are located to being allocated in the front of the file. In other words you need to clear the trailing end of the file.

The alter table move command is useful for this as is alter index rebuild. Both commands require that enough free space exists in the tablespace to hold a second copy of the object while the command is in progress if the target tablespace is not changed.

Moving all the objects out of the tablespace into other tablespaces, shrinking the tablespace datafiles or dropping and recreating it, and then moving the objects back is one option if export/OS space is limited and the tablespace is largely unused and you do not expect to need to reuse the space in the next few weeks/months.

HTH -- Mark D Powell -- Received on Wed Aug 30 2006 - 09:36:09 CDT