RE: TABLESPACE PCTINCREASE?

From: Whittle Jerome Contr NCI <Jerome.Whittle_at_scott.af.mil>
Date: Wed, 06 Feb 2002 09:57:33 -0800
Message-ID: <F001.00407610.20020206081337@fatcity.com>

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). Received on Wed Feb 06 2002 - 11:57:33 CST