Oracle global temp tabs or nologging/append – saved redo in numbers

In an attempt to reduce redo entries (and archive space) generated for only temporarily computed contents of a couple of simple tables, I regarded employing Global Temporary Tables as well as Nologging Inserts as redesign options. This article, however, is not meant to dive into the details of these redesign options at all. There’s plenty of examples and discussions about the pros and cons of each option around the net. Most notably to mention, and a stimulus to post just my redo reduction test results, is Tim Hall’s compact articles on the subject like https://oracle-base.com/articles/misc/temporary-tables and https://oracle-base.com/articles/misc/append-hint that do include an assessment of the redo matter.

So, what’s the lineup for short? On startup, some data will be sourced throughout the database, losely computed and brutely written into a couple of simple tables. Then some third party will read this data for own purposes and will report completion such that the data can eventually be discarded completely. In making shure that an iteration step always starts unsoiled, any data left over from a crashed precursor will be rubbed out as well. Nobody cares about backups of this data, iteration management runs on other tables. You see, this is not really something that takes a database to complete successfully but…

smon exclusive lock on i_obj# for offline temp tbs

did a regular temp tbs exchange lately, you know:

-- create an intermediate tbs
create temporary tablespace tempo
  tempfile 'm:\tempo_01.dbf'  size 512m autoextend on next 128m maxsize 2048m;
-- make it the default for the exchange time
alter database default temporary tablespace tempo;
-- offline and drop the old stuff
alter database tempfile 'm:\temp01.dbf' offline;
drop tablespace temp including contents and datafiles cascade constraints;
-- create the new tbs with the original name
create temporary tablespace temp
  tempfile 'm:\temp_01.dbf' size 512m autoextend on next 128m maxsize 2048m;
-- make this the default again
alter database default temporary tablespace temp;
-- offline and drop the intermediate stuff
alter database tempfile 'm:\tempo_01.dbf' offline;
drop tablespace tempo including contents and datafiles cascade constraints;