Re: Help: Need to defrag a tablespace

Comments interspersed

On Wed, 16 Jan 2002 13:34:32 -0600, Vince Laurent <vlaurent_at_NOSPAMPLEASE.networkusa.net> wrote:

>I am trying to defrag a tablespace. I have a table within a
>tablespace saying it will cause a problem if it tries to go to its
>next extent:
>
>Tablespace Index Table Max Free Total Free Next Ext
>PSAPSTABI AGR_HIERT~0 AGR_HIERT 290,624 2,691,304 327,680
>
>And the stats from the DB02 (Our Database sizing) screen:
>Tablespace Size Free
>PSAPSTABI 6,033,200 2,691,304 55% used
>
>Can I coalesce tablespace with
> ALTER TABLESPACE tablespace COALESCE;
>Will that consolidate all the space?

It will join contiguous free extents into one bigger free extent. It will not move used extents

>
>Or should I do something like this:
>
> Shrinking Datafiles
> As of v7.2, the ALTER DATABASE command can be used to reclaim
>unused space in datafiles. A datafile cannot be resized if the space
>is currently allocated to a database object. (side note: how can I
>tell if it is allocated?)
>
> For example, if a datafile is 100Meg in size, and 70Meg of the
>datafile is currently in use. Then atleast 70Meg must be left in the
>datafile. The RESIZE parameter of the ALTER DATABASE command is used
>to reclaim the space.
> ALTER DATABASE
> datafile '/db01/oracle/fix/data03.ora'
> resize 80M;
> This command can also be used to INCREASE the size of a
>datafile.

You can resize only if all free space is located at the end of the tablespace.

>
>Another thing I found on the web was:
>
> Oracle provides additonal (GUI) applications or "Oracle Packs"
>that extend the funtions of OEM to include monitoring and tuning of
>performace related issues.
>
> Tuning Pack
> Oracle Expert: makes recommendations, and writes the scripts
>to configure and tune your database.
> Tablespace Manager: can coalesce and defrag your
>tablespaces.
> SQL Analyze: For tuning application SQL.
>
>Do these ship with Oracle?
>

No, these are extensions to Oracle Enterprise Manager, you have to pay for them.
Looks like you are scheduled to play with export import. Export all objects in the tablespace, drop the objects, import. This will make sure all used space is contiguous.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jan 16 2002 - 15:42:01 CST