[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
/*********************************************************************************/

[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.

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.

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.

SQL> grant flashback any table to ARCER;

Grant succeeded.

SQL> grant execute on dbms_flashback to ARCER;

Grant succeeded.

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.

SQL> conn fbda_admin/fbda_admin
Connected.

SQL> grant flashback archive on fbda_area_netstore to ARCER;

Grant succeeded.

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表的版本数据信息，就会报错，如下

　　

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.

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.

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中的数据，看看能否查询得到；