首页 > 资讯 > 数据库 >Oracle Study学习之--Flashback Archive

776

分享到

Oracle Study学习之--Flashback Archive

2024-04-02 19:04:59 776人浏览八月长安

摘要

oracle Study学习之--Flashback ArcHiveCREATE FLASHBACK ARCHIVEPurpose Use the&nbs

oracle Study学习之--Flashback ArcHive

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::=

Oracle Study学习之--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 farch2

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 farch2 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 farch2 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 farch2 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 hh34: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 hh34: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 hh34:mi:ss')

15:18:30 5 and

15:18:32 6 to_timestamp('2015-05-11 15:17:33','yyyy-mm-dd hh34: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 hh34:mi:ss')

15:19:59 5 and

15:20:02 6 to_timestamp('2015-05-11 15:17:33','yyyy-mm-dd hh34: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 hh34:mi:ss')

15:20:41 5 and

15:20:44 6 to_timestamp('2015-05-11 15:20:00','yyyy-mm-dd hh34: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 farch2
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 farch2 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 farch2 to scott;
Grant succeeded.

11:20:56 SCOTT@ orcl >alter table emp2 flashback archive farch2;
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 hh34: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读出

11:25:16 SCOTT@ orcl >set autotrace off
11:25:23 SCOTT@ orcl >select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP2 TABLE
SALGRADE TABLE
SYS_FBA_DDL_COLMAP_75889 TABLE
SYS_FBA_HIST_75889 TABLE
SYS_FBA_TCRV_75889 TABLE
T1 TABLE
9 rows selected.
Elapsed: 00:00:00.01

6、建立新的undo tablespace，然后切换
11:19:42 SYS@ orcl >create undo tablespace undotbs1
11:25:57 2 datafile '/u01/app/oracle/oradata/orcl/undotbs1.dbf' size 100m;
Tablespace created.
Elapsed: 00:00:23.73

11:26:41 SYS@ orcl >show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS2
11:26:45 SYS@ orcl >alter system set undo_tablespace=undotbs1;
System altered.
Elapsed: 00:00:00.27

重新启动数据库：
11:27:20 SYS@ orcl >startup force;
ORACLE instance started.

Total System Global Area 205049856 bytes
Fixed Size 1335500 bytes
Variable Size 113250100 bytes
Database Buffers 88080384 bytes
Redo Buffers 2383872 bytes
Database mounted.
Database opened.

再次查询历史数据，因为切换undo tablespace，以前的undo block不能访问，所以会从flashback archive读取数据
11:27:50 @ >conn scott/tiger
Connected.
11:27:58 SCOTT@ orcl >set autotrace on
11:28:02 SCOTT@ orcl >select * from emp2 as of timestamp to_timestamp('2015-05-12 11:21:46','yyyy-mm-dd hh34: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.13

Execution Plan
----------------------------------------------------------
Plan hash value: 3389285906

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 2958 | 16 (7)| 00:00:01 | | |
| 1 | VIEW | | 34 | 2958 | 16 (7)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | PARTITION RANGE SINGLE| | 1 | 113 | 3 (0)| 00:00:01 | KEY | 1 |
|* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_75889 | 1 | 113 | 3 (0)| 00:00:01 | KEY | 1 |
|* 6 | FILTER | | | | | | | |
|* 7 | HASH JOIN OUTER | | 33 | 70191 | 13 (8)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | EMP2 | 33 | 3267 | 9 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | SYS_FBA_TCRV_75889 | 36 | 73008 | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("TIMESTAMP_TO_SCN"(TIMESTAMP' 2015-05-12 11:21:46.000000000')<10558115)
5 - filter("ENDSCN"<=10558115 AND "ENDSCN">"TIMESTAMP_TO_SCN"(TIMESTAMP' 2015-05-12
11:21:46.000000000') AND ("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2015-05-12
11:21:46.000000000')))
6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2015-05-12 11:21:46.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"(+)>10558115) AND ("STARTSCN"(+) IS NULL OR
"STARTSCN"(+)<10558115))

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

Statistics
----------------------------------------------------------
3280 recursive calls
0 db block gets
852 consistent gets
202 physical reads
412 redo size
2489 bytes sent via SQL*Net to client
437 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
57 sorts (memory)
0 sorts (disk)
36 rows processed

从以上执行计划可以看出，访问的是flashback archive ，而不是undo data。

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: Oracle Study学习之--Flashback Archive

本文链接: https://www.lsjlt.com/news/38881.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

猜你喜欢

Oracle Study学习之--Flashback Archive

Oracle Study学习之--Flashback ArchiveCREATE FLASHBACK ARCHIVEPurpose Use the&nbs...

99+

2022-10-18
Oracle Study之案例--数据恢复神器Flashback（3）

Oracle Study之案例--数据恢复神器Flashback（3）Flashback Database：案例分析： flashback database：利用fl...

99+

2022-10-18
Oracle Study之案例--数据恢复神器Flashback（2）

Oracle Study之案例--数据恢复神器Flashback（2）一、Flashback Table:对于DML的误操作，可以通过Undo block对表进行回退（两种模式：基于时间和基于SCN)案例分...

99+

2022-10-18
Oracle Hint 学习之一

APPEDND hint :用于控制insert 语句是否能以直接路径插入的方式插入数据。CACHE hint：用于控制目标sql在执行时是否将全表扫描目标表的数据块放到buffer cache的LRU链表...

99+

2022-10-18
Oracle Hint 学习之三

与表连接顺序的相关hint：执行顺序emp-jobs-deptSQL> select e.ename,j.job,e.sal,d.deptno from&nbs...

99+

2022-10-18
Oracle 学习之卸载Oracle 11g

Oracle 11g 安装完毕后，如果有卸载的需要。可以有如下方法：手工删除Oracle安装时建立的各目录、文件等。这种方法对于Linux还算可行，但是Window版中涉及到服务、注册表等等。...

99+

2022-10-18
Oracle 学习之 11g Clone 安装

如果你需要安装的数据库服务器比较多，使用图形化方式安装数据库软件的速度还是很慢，而且也比较麻烦。Oracle 11g 提供了Clone安装的方法。步骤如下：使用正常方法在服务器A上安装好Ora...

99+

2022-10-18
Oracle菜鸟学习之 Oracle基础命令

首发：http://www.arppinging.com/wordpress/p=93 Oracle菜鸟学习之 Oracle基础命令 1.connect和show user 连接用户和查看当前用户 SQL&...

99+

2022-10-18
Oracle 学习之RAC(四) 安装Oracle软件

上传安装包到11grac1上解压安装包[root@11grac1 database]# unzip p10404530_112030_Linux-x86-64_1of7.zip...

99+

2022-10-18
Oracle学习之DATAGUARD(一) DG架构

DataGuard运行原理非常简单：传输日志、应用日志。下图表示了DG的基本架构日志传输服务将主库产生的日志数据传到从库。应用服务（Apply Service）验证日志...

99+

2022-10-18
Oracle学习之DATAGUARD(十一) snapshot database

Snapshot Standby，此功能可将备库置身于"可读写状态"用于不方便在生产环境主库中测试的内容，比如模拟上线测试等任务。当备库读写状态下任务完成后，可以非常轻松的完成S...

99+

2022-10-18
Oracle 学习之RMAN（十）RMAN维护

我们前面做了那么多的备份，我如何能通过RMAN查询到我所做过的操作呢？RMAN为我们提供了LIST命令。LIST命令可以显示备份集、代理copy、镜像copy的信息。RMAN> ...

99+

2022-10-18
oracle菜鸟学习之表操作

首发：http://www.arppinging.com/wordpress/p=96 oracle菜鸟学习之表操作 1.创建表在oracle中，创建表使用create table来实现 SQL>...

99+

2022-10-18
Oracle GoldenGate学习之--GoldenGate的监控

Oracle GoldenGate学习之--GoldenGate的监控1、使用GGSCI命令监控（1）进入GoldenGate安装目录，运行GGSCI，然后使用info all查看整体的运行状况GGSCI&...

99+

2022-10-18
Oracle 学习之RMAN（五）镜像copy

我们上一讲讲到了RMAN能提供全库备份、表空间备份、数据文件备份、归档日志备份、控制文件及参数文件的备份。RMAN备份出来的文件称之为备份集，RMAN在备份的过程中，只备份已经使用...

99+

2022-10-18
Oracle进阶学习之创建dblink

本文笔者简单说明Oracle创建dblink，dblink就是在一个数据库中直接对另一个数据库进行操作，听起来很美妙，其实并不难实现，我们只需要在该数据库上创建一个dblink关联到另一个数据库就可以了，闲...

99+

2022-10-18
Oracle学习之DATAGUARD(五) 创建logic standby

逻辑备库与物理备库不同，它并不是Primary数据库的一个精确的copy。同一rowid，在逻辑备库上返回的值并不是相同的。有些数据类型或者对象...

99+

2022-10-18
Oracle 学习之 SQL(二) Restricting and Sorting Data

限制SELECT语句的返回行语法：使用WHERE关键字来限制数据行的返回，WHERE子句跟在FROM子句的后面。SQL> select *&nbs...

99+

2022-10-18
Oracle之函数学习以及事务

Oracle之函数学习1、字符函数是oracle中最常用的函数lower(char)：将字符串转化为小写的格式upper(char)：将字符串转化为大写的格式length(char)：返回字符串的长度sub...

99+

2022-10-18
Oracle 学习之RAC(三) Grid Infrastructure 安装

将Grid Infrastructure安装包上传到服务器，并解压unzip p10404530_112030_Linux-x86-64_3of7.zip -d /databa...

99+

2022-10-18