Skip to Main Content

SQL & PL/SQL

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!

on commit delete vs on commit preserve

2988654Mar 25 2016 — edited Mar 25 2016

Hi,

We are using a lot of GTTs in our ETL stored procedures in our new Exadata data warehouse environment. DBAs asked developers to create all GTTs with ON COMMIT PRESERVE setting and truncate them at the beginning of the procs using EXECUTE IMMEDIATE. This didn't seem like a good idea to me so I created my GTTs with ON COMMIT DELETE so that the tables get cleared after each rollback or commit.

I do think that my approach of using on commit delete and not needing to truncate tables explicitly is the right one. But there are hundreds of other GTTs that others have created using ON COMMIT PRESERVE that they truncate in their stored procs. Do you think it's worth convincing them to change the GTT definition of their tables to ON COMMIT DELETE and remove truncate statements from their procs? Are there any major benefits at this point that they would consider making this changes in hundreds of procs?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 22 2016
Added on Mar 25 2016
9 comments
1,868 views