RE: TABLESPACE PCTINCREASE?

From: <Jared.Still_at_radisys.com>
Date: Wed, 06 Feb 2002 10:51:30 -0800
Message-ID: <F001.00407B4E.20020206103828@fatcity.com>

Jerry,

Manual coalescing should not be necessary.

Rather than reformat the manual to be legible in an email, I'll provide link to the section on allocating extents:

http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a88856/c03block.htm#2846

Jared

"Whittle Jerome Contr NCI" <Jerome.Whittle_at_scott.af.mil> Sent by: root_at_fatcity.com
02/06/02 08:13 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: TABLESPACE PCTINCREASE?

The biggest problem with setting the PCTINCREASE to 0 on existing tablespaces is that it won't automatically coalesce. You may need to manually coalesce occasionally especially if you deallocate tables or rebuild indexes. Below is what I use to see tablespaces needing coalescing:

select a.tablespace_name, count(a.tablespace_name) "ContinguousFreeBlocks"

from dba_free_space a, dba_free_space b
where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id
and a.block_id = b.block_id + b.Blocks
group by a.tablespace_name;

If the number of ContinguousFreeBlocks gets above 20, I coalesce. You may chose a different number depending on the size of your tablespaces.

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Whittle Jerome Contr NCI
INET: Jerome.Whittle_at_scott.af.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------