Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Make it a temporary table, perhaps? –  kubanczyk Jul 27 '13 at 6:58
    
Undo logging can not be disabled. –  a_horse_with_no_name Aug 25 '13 at 16:50
    
@a_horse_with_no_name that's no longer true. On 12c temp tables can place undo segment on temp tablespace. –  Mindaugas Riauba Aug 25 '13 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 '13 at 17:49
    
@MindaugasRiauba: putting the undo information into a different tablespace just stores it somewhere else. It's still undo. –  a_horse_with_no_name Aug 25 '13 at 18:32

2 Answers 2

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.

share|improve this answer
    
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) –  a_horse_with_no_name Aug 25 '13 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. –  Mindaugas Riauba Aug 25 '13 at 17:40

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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