Undo tablespace grew 8 times larger the normal within two hours

Database 			Oracle 9.2.0.5 Enterprise Edition
O.S.				Tru64 UNIX.

Hi,

I am an application administrator of a job scheduling application. The job scheduling application uses an Oracle database as repository.

I have created two tablespaces a table, trigger and indexes to log some information about jobs before they get deleted. The job_history table (logging enabled) I have created has been created on a separate dedicated tablespace. The table has 7 columns. All the columns have indexes on them. The indexes are created as nologging (5) B-Tree or nologging (2) Bit Map indexes on a separate dedicated tablespace.

The trigger I have created inserts an entries in the job_history tablespace when the job gets the status deleting, just before the job is actually deleted from the repository. The triggers logs what it should log in the job history table.

The problem I am having is that our undo tablespace has grown from normally 500MB to almost 3500MB in about two hours since the triggers has been enabled. I have disabled the trigger and the undo tablespace has stopped growing. Since the enabling of the trigger there have been about 200 entries logged in the job_history table. The size of the job_history table is a few Bytes.

What are the possible reasons that the undo tablespace grew so large and rapidly? What could I do to prevent the undo tablespace from growing out of proportion? Alter the table to be a nologging table?

Regards,

Mike. Received on Sun Oct 01 2006 - 16:37:39 CDT