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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
