Oracle-L: RE: ALTER TABLE MOVE command causes table to grow

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Fri, 20 Dec 2002 12:20:42 -0800
Message-ID: <F001.00520526.20021220122042@fatcity.com>

Just wanted to follow up on this in the unlikely event that anyone was still wondering.

In retrospect it seems likely that what caused my table to grow while doing the Alter Table Move was the same thing that was causing my problem with new extents being claimed when there was lots of space available in the freelist (same table).

When the move command was issued I'm guessing that for some rows Oracle couldn't find a block on the freelist after the first 5 tries that had enough space for the next row and therefore grabbed another extent.

I'd guess this table is much larger than it needs to be just now. Once we upgrade to 9i this tablespace is a definite candidate for an increase in blocksize...

Jay Miller

-----Original Message-----
Sent: Thursday, September 05, 2002 1:55 PM To: Multiple recipients of list ORACLE-L

Jay:

I would also wonder that the PCTINCREASE was on the table and the indexes. 10% PCTFREE is fine, but does lead to a significant number of empty blocks. What is your PCTUSED? If small, you will have lots of free space within blocks.

Just a thought. Don't let your disk person know this happened as they may try to sell you more hardware.

Thank You

Stephen P. Karniotis
Product Architect
Compuware Corporation

Direct:	(248) 865-4350
Mobile:	(248) 408-2918
Email:	Stephen.Karniotis_at_Compuware.com
Web:	www.compuware.com

 -----Original Message-----
Sent:	Thursday, September 05, 2002 12:09 PM
To:	Multiple recipients of list ORACLE-L
Subject:	ALTER TABLE MOVE command causes table to grow

Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my "medium" tablespace.

The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full).

Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces.

Oracle 8.1.7.2
Solaris 2.6

Thanks,
Jay Miller

--

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

Author: Miller, Jay
INET: JayMiller_at_TDWaterhouse.com

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).

The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

--

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

Author: Karniotis, Stephen
INET: Stephen_Karniotis_at_compuware.com

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

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------