Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

truncate of global temporary tables and UNDO

656121Jan 4 2009 — edited Jan 11 2009
Hi,
My application involves using a lot of global temporary tables while running the application.Most of them are DML operations using insert/delete statements. Due to this activity, the performance is degrading and also my undo tablespace is getting filled up very quickly . We thought of implementing the "truncate" instead of "delete",since unconditional deletes are used. Before implementing the "truncate" in the code, we need the advice from you all in regarding the following.

Ours is 10gR2 using RAC.

1) Do we get any other issues if we use truncate instead of delete for global temporary tables?

2) Does the concept of "two phase commit" have any effect on this?

3) My undo tablespace is almost 99% full most of the time. Since the undo tablespace frees up space as and when required depending on the undo_retention parameter, I guess it is of no problem. But, still do we face any performance problem, since the free space needs to be created in the UNDO tbs by deleting the expired undo, for generating the new UNDO

4) Is there any way of clearing the UNDO tablespace manually?

All the replies are welcome and thanks in advance.

Regards
DRK
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 8 2009
Added on Jan 4 2009
3 comments
863 views