[bbk5102] 第38集 - 第四章 Flashback Database 02
/*********************************************************************************/
实验目的:
1、create tablespace--->fdba_tbs
2、create user ---administer--fbda_amdin
3、grant
4、grant ---ARCER
5、create flashback archive
6、transaction--flashback version query
7、drop undo
8、enable--emp--flashback archive
/*********************************************************************************/
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[oracle@arcerzhang ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Sat May 18 13:13:02 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> create tablespace fbda_tbs 2 datafile '/RealData/oradata/DATACENTER/fbda_tbs01.dbf' size 1G; Tablespace created.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> l 1 create user fbda_admin 2 identified by fbda_admin 3* default tablespace fbda_tbs SQL> / User created. SQL> grant flashback archive administer to fbda_admin; Grant succeeded. SQL> grant connect,resource to fbda_admin; Grant succeeded.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn fbda_admin/fbda_admin Connected. SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE FLASHBACK ARCHIVE ADMINISTER 11 rows selected.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> grant flashback any table to ARCER; Grant succeeded. SQL> grant execute on dbms_flashback to ARCER; Grant succeeded.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn fbda_admin/fbda_admin Connected. SQL> create flashback archive fbda_area_netstore 2 tablespace fbda_tbs 3 quota 100M 4 retention 1 YEAR; Flashback archive created.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn fbda_admin/fbda_admin Connected. SQL> grant flashback archive on fbda_area_netstore to ARCER; Grant succeeded.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn ARCER/ARCER connected SQL> l 1 select versions_xid,versions_starttime,versions_endtime,empno,sal 2 from emp 3 versions between timestamp minvalue and maxvalue 4* where empno < 10 SQL> / VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME EMPNO SAL ---------------- ------------------------- ------------------------- ---------- ---------- 0700200037080000 18-MAY-13 01.44.21 PM 2 1000 06001200F6080000 18-MAY-13 01.43.37 PM 1 1000
--->创建一个新的undo tablespace,并将当前数据库的undo tablespace 设置成为刚刚新创建的undo tablespace;
--->删除之前的undo tablespace
--->再次查询emp表的版本数据信息,就会报错,如下
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> conn ARCER/ARCER Connected. SQL> show user USER is "ARCER" SQL> alter table emp flashback archive fbda_area_netstore; Table altered. SQL> alter table NETSTORE_INCOMEEXPENDITURELIST flashback archive fbda_area_netstore; Table altered.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') cur_date from dual; CUR_DATE ------------------- 2013-05-18 14:13:07 SQL> insert into emp(empno,ename,sal,deptno) values(1,'zhangsan1',1000,10); 1 row created. SQL> commit; Commit complete. SQL> insert into emp(empno,ename,sal,deptno) values(2,'zhangsan2',1000,20); 1 row created. SQL> commit; Commit complete.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> l 1 select versions_xid,to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') versions_starttime,versions_endtime,empno,sal 2 from emp 3 versions between timestamp to_timestamp('2013-05-18 14:13:07','yyyy-mm-dd hh24:mi:ss') and maxvalue 4* where empno < 10 SQL> / VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME EMPNO SAL ---------------- ------------------------- ------------------------- ---------- ---------- 09000100EF080000 2013-05-18 14:13:50 1 1000 03001B0009090000 2013-05-18 14:13:59 2 1000
同样也可以验证,数据是从undo segment中查询到的还是从flashback data archive area查询到的.查询方法就是切换正在使用的undo tablespace,清除undo tablespace中的数据,看看能否查询得到;