Re: Help: Need to defrag a tablespace

And if you have a version of Oracle that supports LOCALLY MANAGED tablespaces ... convert your dictionary manage tablespaces to LOCALLY MANAGED tablespaces using the EXTENT MANAGEMENT LOCAL UNIFORM SIZE option and end tablespace fragmentation forever.

Daniel Morgan

Sybrand Bakker wrote:

> 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 - 17:18:35 CST