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 only takes a minute to sign up.
Sign up to join this community
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.
You can declare table as NOLOGGING. Then some of the DML and DDL operations will not be recorded in redo logs.
Of course this has many caveats:
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.
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.
