We have a specific table that has a lot of activity and it creates a lot of change records. The consequence is that the flashback data only goes back a couple of days. That is OK for many cases but it would be beneficial to have access to more historical data.

We would like to either restrict logging on that table. Or disable it completely. I imagine that we may be able to do this by tablespace, I just have not found much on how to make these changes.

  • Make it a temporary table, perhaps?
    – kubanczyk
    Jul 27, 2013 at 6:58
  • 2
    Undo logging can not be disabled.
    – user1822
    Aug 25, 2013 at 16:50
  • @a_horse_with_no_name that's no longer true. On 12c temp tables can place undo segment on temp tablespace. Aug 25, 2013 at 17:32
  • @a_horse_with_no_name Are you sure-sure? Officially, "RMAN never flashes back data for temporary tablespaces", so I assumed it does not turn this undo into flashback files.
    – kubanczyk
    Aug 25, 2013 at 17:49
  • @MindaugasRiauba: putting the undo information into a different tablespace just stores it somewhere else. It's still undo.
    – user1822
    Aug 25, 2013 at 18:32

3 Answers 3


You can declare table as NOLOGGING. Then some of the DML and DDL operations will not be recorded in redo logs.

Oracle logging_clause

Of course this has many caveats:

  • if you have many NOLOGGING operations basically you won't be able to flashback or restore the table
  • UPDATE or DELETE operations are logged in any case
  • only direct path INSERTS can skip logging. But that means that such INSERT will not reuse empty space in table segment and will extend it every time you do such INSERT.

In my experience in some situations one can save a lot of redo/archivelog space using NOLOGGING but you have to adapt your code to it.

  • The question was about disabling the flashback logging which is essential the undo information. That can not be disabled (NOLOGGING only applies to redo, not undo)
    – user1822
    Aug 25, 2013 at 16:49
  • NOLOGGING also disables undo for direct path inserts. And I mentioned that code will probably be needed to adapt to use this feature. Been there done that. Had to redesign some code to use NOLOGGING direct path insert when DB started to generate 1+TB of redo per day. Aug 25, 2013 at 17:40
  • This is the answer to that question. Nov 5, 2015 at 5:45

1) Before data in the database is modified the original data is stored in the undo tablespace: this enables the rollback of a transaction by undoing the

modification using the original data stored in the undo segments

2) Before data is written to the database it is written to the redo logs: this enables the database to reconstruct its state if the database crashes or if a datafile will be damaged (using a backup and the archive logs that are copies of the redo logs)

These are two different things. Both are necessary tu guarantee the importann ACID properties that are required for a transaction.

Feature 1) has additional advantages:

1) if a statement wants to read data that was modified by another transaction after the statement startet it can find the original data in the undo segments and so can get a consistent view of the data

2) the undo data can be used to retrieve past data from the database (flashback query)

Flashback query is only supported if automatic undo management is enabled which is the default in Oracle 11r2. So contrary to the title of your post for flashback queries the undo information is used and not the redo log information.

In contrast to deletion and update of rows the insert of rows generates a lot of redo but almost no undo information. So it is possible that a sessions generates a lot of redo but not much undo.

I am not aware of any method to find out how much undo information in the undo segment is from a special table but the performance view v$transaction (USED_UBLK: Number of undo blocks used) helps you to identifie the transactions that use much undo space.

If you do not have sufficient old historical data you can try to reduce the undo information written by your applications , keep the transactions short and increase the size of the undo tablespace. If this is not sufficient you can tie a table to a Flashback Data Archive to guarantee that the modified data of the table is kept long enough. Oracle Flashback Data Archive (allso called Oracle Total Recall) is an option of Oracle Enterprise Edition that needs extra licensing.


In addition to miracle173's good answer, here is a thought on limiting the undo impact of the table that while workable probably isn't advisable.

Undo on a table (and therefore the ability to do flashback queries) is removed when a DDL operation occurs for a table. So, if there is a quiet time on the table (during the night for example) you could alter the table (such as by adding a column that you immediately remove). As long as no in progress transaction is occurring on the table this should allow the undo associated with the table to be overwritten therefore allowing more undo from other tables.

No, I don't really think you should try this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.