Skip to main content

Search

Welcome to Tibero GTS!

Example of Using Flashback Table

Document Type | Technical Information

Category | Backup/Recovery

Applicable Product Version | 7FS02PS

Document Number | TBATI002

 

Overview

This explains an example of a Flashback table using the Flashback feature, which is a method to revert a table to a specific point in time or restore a dropped table.
Note
For more details on Flashback table, please refer to β€˜7.70.Flashback Table’ in the β€œTibero 7 Online Manual”.
 

Method

Parameters related to Flashback Table execution

  • DB_RECYCLE_CACHE_SIZE: Parameter that specifies the size of the RECYCLE Buffer Pool (Default: 0)
  • USE_RECYCLEBIN: Parameter that provides recovery functionality in case an object is dropped by user error (Default: N)  -> Must be set to Y
 

1. Sample table and data insert

SQL> conn tibero/tmax
Connected to Tibero.

SQL> Create table flashback_test (col1 number);       
 
Table 'FLASHBACK_TEST' created.
 
SQL> Insert into flashback_test select level as col1 from dual connect by level <= 1000;
 
1000 rows inserted.


SQL> commit;
 
Commit completed.

 

2. Perform Drop Table

SQL> drop table flashback_test;

Table 'FLASHBACK_TEST' dropped.


SQL> select count() from flashback_test;
TBR-8033: Specified schema object was not found.
at line 1, column 23 of null:
select count() from flashback_test
 

3. Check USER_RECYCLEBIN

SQL> select * from user_recyclebin;     

OBJECT_NAME
––––––––––––––––––––––––––––––––––––––––
ORIGINAL_NAME                             TYPE
–––––––––––––––––––––––––––––––––––––––– ––––––––
TS_NAME                                  CREATETIME          DROPTIME               DROPTSN
–––––––––––––––––––––––––––––––––––––––– –––––––––––––––––– ––––––––––––––––––––– ––––––––––
BASE_OBJECT
––––––––––––––––––––––––––––––––––––––––
SPACE
––––––––––––––––––––––––––––––––––––––––
_TIBERO_TBL421500
FLASHBACK_TEST                             TABLE
USR                                      2025-04-28:09:29:05 2025-04-28:09:29:36     133054
 
1 row selected.

 

4. Execute Flashback Table query

SQL> flashback table flashback_test to before drop;

Flashbacked.
 
SQL> select count(*) from flashback_test;
 
  COUNT(*)
––––––––––
      1000
 
1 row selected.

 

 

 

Related to