Help: Need to defrag a tablespace

From: Vince Laurent <vlaurent_at_NOSPAMPLEASE.networkusa.net>
Date: Wed, 16 Jan 2002 13:34:32 -0600
Message-ID: <7blb4uo240lhjd02un06undbqmdk3a17dr@4ax.com>

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?

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.

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? Received on Wed Jan 16 2002 - 13:34:32 CST