 |
|
undo retention guarantee tips
Oracle Tips by Burleson |
Oracle 10g
undo retention guarantee Parameter
Data used to recover a table is stored in the
undo tablespace. You can use the parameter undo_retention to set
the amount of time you want undo information retained in the
database.
To create an undo tablespace with the RETENTION
GUARANTEE option, issue the following command:
CREATE UNDO
TABLEAPCE
undo_tbs
DATAFILE
‘/u02/oradata/grid/undo_tbs01.dbf’
SIZE 1 G
RETENTION GUARANTEE;
Guaranteed Retention
When an active transaction uses all the available
undo tablespace, the system will start reusing undo space that would
have been retained, unless you have specified RETENTION GUARANTEE
for the tablespace.
Flashback Table Privileges
You must have the FLASHBACK TABLE or FLASHBACK
ANY TABLE system privilege to use the Flashback Table feature.
Example 1: Flashback Table using SCN
This statement brings a table ‘billing’ back to
a certain SCN number; table row movement must be enabled as a
prerequisite:
SQL>
FLASHBACK TABLE billing TO SCN 76230;
Example 2: Flashback Table using TIMESTAMP
This statement brings a table ‘billing’ back to
a certain timestamp:
SQL>
FLASHBACK TABLE billing
TO
TIMESTAMP
TO_TIMESTAMP(‘06/25/03 12:00:00’,’MM/DD/YY HH:MI:SS’);
|
