Re: Undo tablespace grew 8 times larger the normal within two hours

On Sun, 01 Oct 2006 23:37:39 +0200, Mike <m.leyden_at_chello.nl> wrote:

>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.

Please read up on the impact of nologging in the documentation. It doesn't have the impact you think it has. And why did you put indexes on all columns? What purpose does that serve?
Oracle recommends putting only 5 indexes on any table. This is easy to understand, as due to any INSERT, UPDATE or DELETE the indexes need to be changed as well. And, YES, those changes are logged. Also a bit map index requires at least a complete block to be maintained, so the volume of redo generated will be higher.

--
Sybrand Bakker, Senior Oracle DBA