Re: Unexplained table growth

If I were you I'd try to analyze both tables (prod and test), posibly on a small sample, to check whether the average row length is identical. The 'comment' field, which can be close enough to 60%, may be NULL in your test database and full in most cases in prod. Something else one could think of is a PCTFREE difference. It may be higher on your prod database. There may also be differences in the per-tablespace minimum allocation values.

S Faroult

"Jesse, Rich" wrote:
>
> 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!
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).