Unexplained table growth

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Fri, 18 Jan 2002 11:47:48 -0800
Message-ID: <F001.003F3E18.20020118094045@fatcity.com>

Hi all,

8.1.7.2.0 on HP/UX 11.0 -- A developer tested a table recreate in our test DB. Here's the scenario:

CREATE TABLE MY_BIG_TABLE (
FISCAL_YEAR NUMBER (5),

  PERIOD        CHAR (2), 
  ACCOUNTNO     CHAR (12), 
  TRANSTYPE     CHAR (2), 
  TRANSQTY      FLOAT, 
  TRANSAMNT     FLOAT, 
  COMMENT_TEXT  CHAR (30), 
  TRANSDATE     DATE)

   TABLESPACE QT_APPS1
   PCTFREE 10
   PCTUSED 40
   INITRANS 2
   MAXTRANS 255
  STORAGE (
   INITIAL 209715200
   NEXT 10485760
   PCTINCREASE 0
   MINEXTENTS 1
   MAXEXTENTS 249
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE; The table previously had a total of 14 extents, giving it a size of 330MB. The dev TRUNCATED the table (I don't know if "REUSE STORAGE" was used), and added 4,054,632 (4M) rows. The table is still at 14 extents and 330MB.

So, all's well in test, the dev did the same in production. The row count was *slightly* higher, at 4.069,106, but the table size jumped 60% to 560MB. The DB_BLOCK_SIZE on both DBs is 8K. Of course, this filled up the TBS and caused havoc.

I looked in DBA_AUDIT_TRAIL and DBA_TAB_MODIFICATIONS, but I can't find any significant difference between what was done to the table in test and prod. The table in production has NO deletes recorded in DBA_TAB_MODIFICATIONS, just inserts.

Can anyone think of a scenario as to why this table would grow in prod but not test with relatively the same number of rows and the exact same table layout??? The only thing I can think of is that a "REUSE STORAGE" was issued on one TRUNCATE, but not another, but I still don't see how that could account for the table growth.

I'm going thru LogMiner now, but as our test DB is in archivelog mode, I can only look in production (and it's taking forever!).

TIA!

Rich Jesse                              System/Database Administrator
Rich.Jesse_at_qtiworld.com                 Quad/Tech International, Sussex, WI

USA
--

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

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.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). Received on Fri Jan 18 2002 - 13:47:48 CST