Hi, Jesse, Didn't your dev use parallel insert on production to speedup things? This may cause different segment allocation HTH Vadim
-----Original Message----- Sent: Friday, January 18, 2002 12:41 PM To: Multiple recipients of list ORACLE-L 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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vadim Gorbounov 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).
