Oracle 11g new features flashback Data Archive usages _oracle

Flashback Data Archive (Flashback log archive) is actually understood as a long time to save undo data, for some important tables can customize its history to save the time, its behavior and undo table space is very similar to the use of the time is completely transparent, The user does not know whether its query-consistent view data is from undo or flashback, and now analyzes its differences from undo:

The 1.Flashback Data archive only records update and DELETE statements and does not record INSERT statements.

The row database in the 2.Flashback data archive can be stored for a very long time, or even decades, in contrast to the objects in the undo data that typically have a retention period of several hours or days.

The essential function of 3.Flashback Data archive and undo is different, it only focuses on the historical changes of the table rows, rather than undo to achieve the read consistency of the database overall transaction, has been rolled back operation, etc.

So how does the Flashback Data archive be implemented, assuming that you are saving row records in a table, you should need a separate storage area to record the rows, specify a retention period for that area, and then add trace tags to the table you want to record. So that you can save the archive of the Row Records, which is actually the principle of Oracle, where the storage area is flashback Data Archive, created using the Create Flashback Archive command during use.

First, create an archive

Oracle recommends using a separate tablespace to store flashback data Archive, and of course, you can create one or more flashback data Archive on an existing tablespace.

– You need to ensure that the execution user has Flashback_archive_administrer permissions before it is created, including creating and modifying flashback ARCHIVE, enabling table tracking, managing tablespaces in the archive, and so on.

Copy Code code as follows:

sql> Create Tablespace fbda1
2 datafile '/u01/app/oracle/oradata/prod/fbda01.dbf '
3 size 5g;

Tablespace created.

Next you start to create flashback Data Archive:

Copy Code code as follows:

Sql> Create Flashback Archive fb_01
2 tablespace fbda1 Quota 300m
3 retention year;

Flashback archive created.

The above command creates a 10-year flashback Data Archive that can use 300m of space in the FBDA1 table space (a bit small ...). , we can then create a default archive, as needed, using the defaults keyword, such as

Copy Code code as follows:

Sql> Create Flashback archive default Fb_dflt
2 tablespace fbda1
3 Retention 1 year
4/

Flashback archive created.

Query Dba_flashback_archive View get archived information, including retention period, status, etc.

Copy Code code as follows:

Sql> Select Owner_name,flashback_archive_name,
2 Flashback_archive#,retention_in_days,status
3 from Dba_flashback_archive;

Owner_name flashback_archive_name flashback_archive# retention_in_days STATUS
---------- ---------------------- ------------------ ----------------- -------
SYS fb_01 1 3650
SYS Fb_dflt 2 365 DEFAULT

Query Flashback_archive_ts view get table space and archive correspondence

Copy Code code as follows:

Sql> select * from Dba_flashback_archive_ts;

Flashback_archive_name flashback_archive# Tablespace_name QUOTA_IN_MB
------------------------- ------------------ -------------------- ------------
FB_01 1 FBDA1 300
Fb_dflt 2 FBDA1

Second, the tracking table to the specified flashback Data archive

Assign the Scott user's EMP table to the FB_01 archive:

Copy Code code as follows:

Sql> ALTER TABLE scott.emp Flashback archive fb_01;

Table altered.

Query Dba_flashback_archive_tables view to obtain a table that has already been archived:

Copy Code code as follows:

Sql> select * from Dba_flashback_archive_tables;

table_name owner_name Flashback_archive_na Archive_table_name STATUS
---------- ---------- -------------------- -------------------- --------
EMP SCOTT fb_01 sys_fba_hist_73181 ENABLED

Third, the query using flashback Data Archive

There's nothing wrong with using and undo here, you can specify as an in the query, and here's how to query the EMP table data from 3 years ago:

Copy Code code as follows:

Sql> Select Empno,ename,hiredate
2 from Scott.emp
3 As of timestamp (Systimestamp-interval ' 3 ' year)
4 where empno=7934;

EMPNO ename HireDate
---------- ---------- ---------
7934 MILLER 23-jan-82