Transactions requires much more UNDO space than expected....

Oracle 9.2.0.6 EE on W2K.

As part of a table maintenance operation I am copying the contents of a working table into a main table with the following command:

INSERT INTO PS_TL_PAYABLE_TIME SELECT * FROM PS_TY_TL_PAY_T_TMP; Based the the following info:

PS_TY_TL_PAY_T_TMP row count: 12 Million PS_TY_TL_PAY_T_TMP avg row len: 191 bytes

Indexes on the main table (ps_tl_payable_time):

INDEX_NAME                       COLUMN_NAME
PSATL_PAYABLE_TIME        SEQ_NBR

PSBTL_PAYABLE_TIME        FROZEN_DATE

PSCTL_PAYABLE_TIME        EMPLID,TRC,DUR

PSDTL_PAYABLE_TIME        DUR,TRC

PS_TL_PAYABLE_TIME        EMPLID, EMPL_RCD,DUR,SEQ_NBR

I would have exptected the UNDO required for this statement to be the ROWID (10 bytes) for every row in the table plus the ROWID for every row in the index plus a small overhead.

That works out as:

12,000,000 * 10 bytes = 120Mb for the table 12,000,000 * 10 bytes = 120Mb for each index

=> Approx 720Mb of UNDO.

Does that 'approximate' calculation seem correct..? Because in practice that query manages to fill up a 4Gb UNDO tablespace...

Am I missing something here...?!?!

Thanks

Matt Received on Mon Jun 19 2006 - 05:40:26 CDT