Flashback scripts for Cool performance tests

20 Jun 2007

First of all, once the tables are created we should enable row movement on them and grant the flashback privilege to the user.

From doc :


To flash back a table to an earlier SCN or timestamp, you must have either the FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege. In addition, you must have the SELECT, INSERT, DELETE, and ALTER object privileges on the table.

Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table TO BEFORE DROP. That operation is called a flashback drop operation, and it uses dropped data in the recyclebin rather than undo data. Please refer to row_movement_clause for information on enabling row movement.

To flash back a table to a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

To flash back a table to before a DROP TABLE operation, you need only the privileges necessary to drop the table.


Specify a restore point to which you want to flash back the table. The restore point must already have been created.

See Also: CREATE RESTORE POINT for information on creating restore points

Note: Oracle strongly recommends that you run your database in automatic undo mode by setting the UNDO_MANAGEMENT initialization parameter to AUTO. In addition, set the UNDO_RETENTION initialization parameter to an interval large enough to include the oldest data you anticipate needing. For more information please refer to the documentation on the UNDO_MANAGEMENT and UNDO_RETENTION initialization parameters.


Grant succeeded.

SQL> ALTER TABLE Table1 enable row movement;

Table altered.


Restore point created.


Flashback complete.

21 Jun 2007

After successfully creating the scripts we spotted a big performance issue. For big tables the process takes way too much time.


Flashback complete.

Elapsed: 00:38:59.80

SQL> select sum(bytes)/power(2,30)||' GB' "PDBST001 size" from user_extents where segment_name like 'PDBST001%';

PDBST001 size
33.99652099609375 GB
This was for a flashback with no change to do...

Now let's try to do the same on one sample table with modifications and compare the hand-made reverse tool to flashback table.

25 Jun 2007

Actually the performance tests done by Gianni are usually using only 1 folder, so it would be a lot quicker if we flashback only a few tables.

SQL> flashback table pdbst005_f0001_iovs, pdbst005_f0001_iovs_seq, pdbst005_f0001_channels to restore point test;

Flashback complete.

Elapsed: 00:03:19.62
For 3000 iovs inserted by channel (200 channels) :
SQL> delete from PDBST005_F0001_IOVS where iov_since > 12960300006;

600000 rows deleted.

Elapsed: 00:00:41.83

The flashback technology still takes more time but perhaps it is not an issue anymore since usually only one table is affected by the test.

So the flashback solution is still possible.

-- RomainBasset - 25-Jul-2007

Topic attachments
I Attachment History Action Size Date Who Comment
Unknown file formatsql CREATE_RESTORE_POINT.sql r1 manage 0.4 K 2007-06-21 - 10:21 RomainBasset  
Unknown file formatsql DROP_RESTORE_POINT.sql r1 manage 0.4 K 2007-06-21 - 10:21 RomainBasset  
Unknown file formatsql GENERATE_FLASHBACK_TABLE.sql r1 manage 0.9 K 2007-06-21 - 10:20 RomainBasset  
Unknown file formatsql GENERATE_ROW_MOVEMENT.sql r1 manage 0.7 K 2007-06-21 - 10:21 RomainBasset  
Edit | Attach | Watch | Print version | History: r11 < r10 < r9 < r8 < r7 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r11 - 2010-06-18 - AndreaValassi
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    Persistency All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2023 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback