Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ALTER TABLE MOVE command causes table to grow

RE: ALTER TABLE MOVE command causes table to grow

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Wed, 11 Sep 2002 11:48:28 -0400
Message-Id: <22541.293564@fatcity.com>


Clever thought, but I should have supplied the size before.

It grew from app. 4Gig to app. 6Gig.

That's why I was so surprised, growing 2 Gig during a Move was rather unexpected.

-----Original Message-----
From: Seefelt, Beth [mailto:Beth.Seefelt_at_TetleyUSA.com] Sent: Tuesday, September 10, 2002 4:03 PM To: Multiple recipients of list ORACLE-L Subject: RE: ALTER TABLE MOVE command causes table to grow

I hope this isn't overly simplistic but, is it because of the larger extent size?

For instance, if it was in 4m extents before and used 26M, it would have fit in 7 extents with 2M of free space.

But when moved to 25M extents, it would use 2 extents with 24M free space.

How many segments is the table using, and what's the real size ( ie. sum(bytes))?

Beth

-----Original Message-----
Sent: Tuesday, September 10, 2002 2:43 PM To: Multiple recipients of list ORACLE-L

DEGREE=1 -----Original Message-----
Sent: Monday, September 09, 2002 8:38 PM To: Multiple recipients of list ORACLE-L

What is the DEGREE setting on the table?

-----Original Message-----
Sent: Monday, September 09, 2002 4:48 PM To: Multiple recipients of list ORACLE-L

alter table email_request_queue
move
tablespace ts_email_request
storage (initial 25m next 25m pctincrease 0);

-----Original Message-----
Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L

How did you perform the move? Was the operation done in parallel?

-----Original Message-----
Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L

No LOBS. Here's the definition:

 REQUEST_ID                                NOT NULL NUMBER
 PROFILE_ID                                NOT NULL NUMBER
 ACCOUNT_ID                                NOT NULL NUMBER
 TEMPLATE_ID                               NOT NULL NUMBER
 GENERIC_DIFFERENTIATOR                    NOT NULL NUMBER
 REQUEST_TYPE                              NOT NULL CHAR(1)
 ACCOUNT_NO                                NOT NULL CHAR(8)
 EFFECTIVE_DATE                            NOT NULL DATE
 EMAIL_ADDRESS                             NOT NULL VARCHAR2(100)
 EMAIL_SUBJECT                             NOT NULL VARCHAR2(100)
 EMAIL_BODY                                NOT NULL VARCHAR2(4000)
 STATUS                                    NOT NULL CHAR(1)
 STATUS_CHANGE_DATE                        NOT NULL DATE
 TWEED_SERVER_ID                                    NUMBER
 TWEED_PACKAGE_PRIORITY                             NUMBER
 TWEED_SENDER_ACCOUNT                      NOT NULL VARCHAR2(50)
 TWEED_SCHEDULED_DELIVERY_DATE             NOT NULL DATE
 TWEED_CUSTOMER_URL                        NOT NULL VARCHAR2(255)
 SENDER_SERVER_ID                                   NUMBER
 SENDER_INSTANCE_ID                                 NUMBER
 CREATE_DATE                               NOT NULL DATE
 CREATE_USER                               NOT NULL VARCHAR2(35)
 UPDATE_DATE                                        DATE
 UPDATE_USER                                        VARCHAR2(35)


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

What the table definition? Are there any LOB's on it?

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

pct increase is 0 (uniform sizing)

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

you didn't mention the PCT_INCREASE of this segment.

-----Original Message-----
<mailto:JayMiller_at_tdwaterhouse.com> ]
Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L

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

--

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

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

Author: Johnston, Tim
  INET: TJohnston_at_quallaby.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).
--

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

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

Author: Johnston, Tim
  INET: TJohnston_at_quallaby.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).
--

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

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

Author: Johnston, Tim
  INET: TJohnston_at_quallaby.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).
--

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

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

Author: Seefelt, Beth
  INET: Beth.Seefelt_at_TetleyUSA.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 Received on Wed Sep 11 2002 - 10:48:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US