当前位置：Gxlcms > 数据库问题 > Oracle Study学习之--Flashback Archive

Oracle Study学习之--Flashback Archive

时间：2021-07-01 10:21:17 帮助过：15人阅读

CREATE FLASHBACK ARCHIVE

Purpose

Use the CREATE FLASHBACK ARCHIVE statement tocreate a flashback data archive, which provides the ability to automaticallytrack and archive transactional data changes to specified database objects. Aflashback data archive consists of multiple tablespaces and stores historicdata from all transactions against tracked tables.

Flashback dataarchives retain historical data for the time duration specified using the RETENTION parameter.Historical data can be queried using the Flashback Query AS OF clause.Archived historic data that has aged beyond the specified retention period isautomatically purged.

Flashback dataarchives retain historical data across data definition language (DDL) changesto tables enabled for flashback data archive. Flashback data archives supportsa lot of common DDL statements, including some DDL statements that alter tabledefinitions or incur data movement. DDL statements that are not supportedresult in error ORA-55610.

Syntax

create_flashback_archive::=

技术分享

一、建立flashbackarchive

1、建立flashback专用表空间

14:33:13 SYS@ orcl >create tablespaceflash_tbs

14:33:26 2 datafile‘/u01/app/oracle/oradata/orcl/flash_tbs01.dbf‘ size 100m;

Tablespace created.

Elapsed: 00:00:22.03

2、建立flashback archive

14:36:16 SYS@ orcl >create flashbackarchive farch1

14:36:54 2 tablespace flash_tbs

14:36:58 3 quota 20m

14:37:01 4 retention 1 month;

Flashback archive created.

3、修改flashback archive属性

14:37:09 SYS@ orcl >alter flashbackarchive farch1 set default;

Flashback archive altered.

Elapsed: 00:00:00.15

4、查看flashback archive属性

14:40:13 SYS@ orcl >colFLASHBACK_ARCHIVE_NAME for a20

14:40:25 SYS@ orcl >select FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS ,STATUS fromdba_flashback_archive;

FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYSSTATUS

-------------------- ------------------------

FARCH1 30 DEFAULT

Elapsed: 00:00:00.01

14:41:54 SYS@ orcl >col QUOTA_IN_MB fora10

14:42:02 SYS@ orcl >select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_M

-------------------- ------------------------------------------------ ----------

FARCH1 1FLASH_TBS 20

5、添加tablespace到flashback archive

14:42:03 SYS@ orcl >create tablespaceflash_tbs2

14:42:36 2 datafile‘/u01/app/oracle/oradata/orcl/flash_tbs2a.dbf‘ size 100m;

Tablespace created.

14:43:51 SYS@ orcl >alter flashbackarchive farch1 add tablespace flash_tbs2;

Flashback archive altered.

Elapsed: 00:00:00.22

14:44:05 SYS@ orcl >select * fromdba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE#TABLESPACE_NAME QUOTA_IN_M

-------------------- ------------------------------------------------ ----------

FARCH1 1 FLASH_TBS 20

FARCH1 1 FLASH_TBS2

Elapsed: 00:00:00.00

二、在表上启用flashbackarchive

1、授予权限

15:00:11 SYS@ orcl >select * fromsystem_privilege_map where NAME like ‘%FLASHBACK%‘;

PRIVILEGE NAME PROPERTY

-------------------------------------------------- ----------

-243 FLASHBACK ANY TABLE 0

-350 FLASHBACK ARCHIVE ADMINISTER 0

Elapsed: 00:00:00.03

15:00:38 SYS@ orcl >grant flashbackarchive on farch1 to scott;

Grant succeeded.

15:04:50 SCOTT@ orcl >alter table emp2flashback archive;

Table altered.

15:11:13 SCOTT@ orcl >select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

BONUS TABLE

DEPT TABLE

DEPT1 TABLE

EMP TABLE

EMP1 TABLE

EMP2 TABLE

EMP3 TABLE

SALGRADE TABLE

SYS_FBA_DDL_COLMAP_75889 TABLE

SYS_FBA_HIST_75889 TABLE

SYS_FBA_TCRV_75889 TABLE

三、 flashback archive 应用

1）访问历史数据

15:09:01SCOTT@ orcl >set autotrace on

15:09:08SCOTT@ orcl >r

1* select * from emp2 as of timestampto_timestamp(‘2015-05-11 15:05:04‘,‘yyyy-mm-dd hh24:mi:ss‘)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ------------ ---------- ---------- ----------

7566 JONES MANAGER 7839 02-APR-81 2975 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7369 SMITH CLERK 7902 17-DEC-80 800 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

14 rowsselected.

Elapsed:00:00:00.03

ExecutionPlan

----------------------------------------------------------

Plan hashvalue: 3389285906

----------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 17 | 1479 | 11 (10)| 00:00:01 | | |

| 1 | VIEW | | 17 | 1479 | 11 (10)| 00:00:01 | | |

| 2 | UNION-ALL | | | | | | | |

|* 3 | FILTER | | | | | | | |

| 4 | PARTITION RANGE SINGLE| | 1 | 113 | 2 (0)| 00:00:01 | KEY | 1 |

|* 5 | TABLE ACCESS FULL |SYS_FBA_HIST_75889 | 1 | 113 | 2 (0)| 00:00:01 | KEY | 1 |

|* 6 | FILTER | | | | | | | |

|* 7 | HASH JOIN OUTER | | 16 | 34032 | 9 (12)| 00:00:01 | | |

|* 8 | TABLE ACCESS FULL | EMP2 | 16 | 1584 | 5 (0)| 00:00:01 | | |

|* 9 | TABLE ACCESS FULL |SYS_FBA_TCRV_75889 | 4 | 8112 | 3 (0)| 00:00:01 | | |

----------------------------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

3 -filter("TIMESTAMP_TO_SCN"(TIMESTAMP‘ 2015-05-1115:05:04.000000000‘)<10456170)

5 - filter("ENDSCN"<=10456170AND "ENDSCN">"TIMESTAMP_TO_SCN"(TIMESTAMP‘ 2015-05-11

15:05:04.000000000‘) AND("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP‘2015-05-11

15:05:04.000000000‘)))

6 -filter("STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP‘2015-05-11 15:05:04.000000000‘) OR "STARTSCN"

IS NULL)

7 -access("T".ROWID=CHARTOROWID("RID"(+)))

8 -filter("T"."VERSIONS_STARTSCN" IS NULL)

9 - filter(("ENDSCN"(+) IS NULL OR"ENDSCN"(+)>10456170) AND ("STARTSCN"(+) IS NULL OR

"STARTSCN"(+)<10456170))

Note

-----

- dynamic sampling used for this statement(level=2)

Statistics

----------------------------------------------------------

78 recursive calls

0 db block gets

111 consistent gets

0 physical reads

0 redo size

1421 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

14 rows processed

15:09:11 SCOTT@ orcl >

2）生成统计报表

15:12:07SCOTT@ orcl >select * from emp2

15:14:53 2 versions

15:14:57 3 between timestamp

15:15:16 4 to_timestamp(‘2015-05-11 15:05:04‘,‘yyyy-mm-dd hh24:mi:ss‘)

15:15:29 5 andmaxvalue

15:15:34 6 where ename=‘SCOTT‘;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.01

15:15:45 SCOTT@ orcl >

3）信息生命周期管理

15:15:45SCOTT@ orcl >select count(*) from emp2;

COUNT(*)

----------

Elapsed:00:00:00.00

15:17:22SCOTT@ orcl >insert into emp2 select * from emp2;

18 rowscreated.

Elapsed:00:00:00.00

15:17:31SCOTT@ orcl >commit;

Commit complete.

Elapsed:00:00:00.09

15:17:33SCOTT@ orcl >select count(*) from emp2;

COUNT(*)

----------

Elapsed:00:00:00.00

15:17:37SCOTT@ orcl >select * from emp2

15:18:00 2 versions

15:18:06 3 between timestamp

15:18:13 4 to_timestamp(‘2015-05-11 15:05:04‘,‘yyyy-mm-dd hh24:mi:ss‘)

15:18:30 5 and

15:18:32 6 to_timestamp(‘2015-05-11 15:17:33‘,‘yyyy-mm-dd hh24:mi:ss‘)

15:19:06 7 where empno=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.00

15:19:13SCOTT@ orcl >select * from emp2 where empno=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.00

15:19:25SCOTT@ orcl >select * from emp2

15:19:42 2 versions

15:19:45 3 between timestamp

15:19:48 4 to_timestamp(‘2015-05-0915:05:04‘,‘yyyy-mm-dd hh24:mi:ss‘)

15:19:59 5 and

15:20:02 6 to_timestamp(‘2015-05-11 15:17:33‘,‘yyyy-mm-dd hh24:mi:ss‘)

15:20:06 7 where empno=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.02

15:20:09SCOTT@ orcl >select * from emp2

15:20:27 2 versions

15:20:30 3 between timestamp

15:20:37 4 to_timestamp(‘2015-05-09 15:05:04‘,‘yyyy-mm-dd hh24:mi:ss‘)

15:20:41 5 and

15:20:44 6 to_timestamp(‘2015-05-11 15:20:00‘,‘yyyy-mm-dd hh24:mi:ss‘)

15:21:01 7 where empno=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.01

案例测试：

flashback archive：

1、建立flashback archive 专用tablespace

11:15:32 SYS@ orcl >create tablespace ftbs1
11:15:40 2 datafile ‘/u01/app/oracle/oradata/orcl/ftbs1.dbf‘ size 100m;

Tablespace created.
Elapsed: 00:00:25.14

2、建立flashback archive
11:16:17 SYS@ orcl >create flashback archive farch1
11:16:41 2 tablespace ftbs1
11:17:14 3 quota 20m
11:17:26 4 retention 7 day;

Flashback archive created.
Elapsed: 00:00:00.28

3、查询属性
11:17:49 SYS@ orcl >select FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS ,STATUS from dba_flashback_archive
11:18:07 2 ;

FLASHBACK_ RETENTION_IN_DAYS STATUS
---------- ----------------- -------
FARCH1 7

Elapsed: 00:00:00.01
11:18:08 SYS@ orcl >select * from dba_flashback_archive_ts;

FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_M
---------- ------------------ ------------------------------ ----------
FARCH1 1 FTBS1 20

Elapsed: 00:00:00.01

11:18:37 SYS@ orcl >alter flashback archive farch1 set default;
Flashback archive altered.
Elapsed: 00:00:00.19

11:18:56 SYS@ orcl >select FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS ,STATUS from dba_flashback_archive;

FLASHBACK_ RETENTION_IN_DAYS STATUS
---------- ----------------- -------
FARCH1 7 DEFAULT

Elapsed: 00:00:00.01

4、授权用户在table启用flashback archive
11:19:11 SYS@ orcl >grant flashback archive on farch1 to scott;
Grant succeeded.

11:20:56 SCOTT@ orcl >alter table emp2 flashback archive farch1;
Table altered.
Elapsed: 00:00:00.31

11:21:14 SCOTT@ orcl >select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP2 TABLE
SALGRADE TABLE
T1 TABLE
6 rows selected.
Elapsed: 00:00:00.01

11:21:46 SCOTT@ orcl >delete from emp2;
36 rows deleted.
Elapsed: 00:00:00.03

11:22:05 SCOTT@ orcl >commit;
Commit complete.
Elapsed: 00:00:00.19

11:22:08 SCOTT@ orcl >select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP2 TABLE
SALGRADE TABLE
T1 TABLE
6 rows selected.
Elapsed: 00:00:00.00

11:22:12 SCOTT@ orcl >select * from emp2;
no rows selected
Elapsed: 00:00:00.00

4、查询flashback archive数据

默认，Oracle 优先从undo block读取历史数据，若undo block数据不存在，然后再去访问 flashback archive。
11:24:06 SCOTT@ orcl >set autotrace on
11:24:16 SCOTT@ orcl >select * from emp2 as of timestamp to_timestamp(‘2015-05-12 11:21:46‘,‘yyyy-mm-dd hh24:mi:ss‘);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
36 rows selected.
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2941272003

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 654 | 56898 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP2 | 654 | 56898 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2655 bytes sent via SQL*Net to client
437 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

从以上sql可以看到，数据是从undo data读出

< 上一篇
MySQL中的datetime与timestamp比较

下一篇 >
CHARINDEX (Transact-SQL)

人气教程排行

229次 1 oracle 用户解锁和修改用户密码

229次 2 解决：安装SQL Server 2008 Native Client遇到错误（在Navicat premium新建sqlserver连接时需要）：An error occurred during ...HRESULT: 0x80070422（注意尾部的错误号）

229次 3 rocksdb 编译步骤

229次 4 adb命令开关蓝牙及NFC

229次 5 解决数据库连接错误您在wp-config.php文件中提供的数据库用户名和密码可能不正确，或者无法连接到localhost上的数据库服务器，这意味着您的主机数据库服务器已停止工作。

229次 6 使用java自动填充，实现mysql的创建修改时间的自动填充

229次 7 Linux mount挂载磁盘报错 mount: wrong fs type, bad option, bad superblock on /dev/vdb

228次 8 关于mysql创建数据库中字符集和排序规则的选择

228次 9 Linux设置Mysql开机自启动服务

227次 10 SQL2008：WITH MOVE 子句可用于重新定位一个或多个文件

227次 11 图数据库Neo4j在GIS系统的应用

227次 12 SpringBoot配置 druid 数据源配置慢SQL记录

227次 13 SQL state [72000]; error code [1013]; ORA-03111: 通信通道收到中断; java.sql.SQLException: ORA-01745: 无效的主机/绑定变量名;java.sql.SQLException: ORA-01013: 用户请求取消当前的操作

227次 14 docker-compose启动MySQL并配置远程登录

225次 15 Mysql安装（for mac）

225次 16 kettle将图片转换至二进制存储至数据库

225次 17 adb的安装及配置

224次 18 使用PHP控制MODBUS-RTU设备

224次 19 MySQL 查询时间差值大于某一个值的记录

224次 20 MongoDB内存配置 --wiredTigerCacheSizeGB