09 January 2007

Temporary Tables and REDO Logs

Today in DBA class, the instructor matter-of-factly rattled off some characteristics on global temporary tables. Among these I just barely noticed him state that they generate REDO logging. I sat upright and asked him to confirm that I had heard him correctly, and indeed that is what he said. He acknowledged that it runs contrary to what you'd think about an object that holds session-specific data that cannot be recovered anyway.

Well I just did some googling and found out exactly what the case is, from Oracle 10gR2 Database Concepts:

"DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated."

So it is sort of an indirect redo log generation. Still, something to be aware of. The instructor recalled how he once had a user filling up the archived log destination disk by running a lot of activity just on a global temporary table.

No comments:

Post a Comment