IT开放社区

UNDO表空间配置及闪回版本查询

环境:

OS:Oracle Linux Server release 5.6

DB:Oracle 11.2.0.1

可用于闪回的版本:

每个事物的开始到提交都会在UNDO中生成一个版本,在使用闪回时可基于时间或SCN闪回到某个指定的版本;

1.配置UNDO表空间,查看当前数据库UNDO表空间状态;

itkaifang@SYS> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
itkaifang@SYS>

创建新的UNDO表空间;

itkaifang@SYS> create undo tablespace myundo datafile'/u01/app/oracle/oradata/itkaifang/undotbs02.dbf' size 10M;
Tablespace created.
itkaifang@SYS>

查询当前表空间;

itkaifang@SYS> select tablespace_name,contents from dba_tablespaces;
TABLESPACE_NAME                CONTENTS
------------------------------ ---------
SYSTEM                         PERMANENT
SYSAUX                         PERMANENT
UNDOTBS1                       UNDO
TEMP                           TEMPORARY
USERS                          PERMANENT
EXAMPLE                        PERMANENT
MYUNDO                         UNDO
7 rows selected.
itkaifang@SYS>

切换UNDO表空间;

itkaifang@SYS> alter system set undo_tablespace=myundo;
System altered.
itkaifang@SYS> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      MYUNDO
itkaifang@SYS>

删除UNDO表空间;

itkaifang@SYS> drop tablespace myundo;
Tablespace dropped.
itkaifang@SYS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@itkaifang ~]$ cd /u01/app/oracle/oradata/itkaifang
[oracle@itkaifang itkaifang]$ ls
control01.ctl  redo01.log  sysaux01.dbf  temp01.dbf     undotbs02.dbf
control02.ctl  redo02.log  system01.dbf  temp101.dbf    users01.dbf
example01.dbf  redo03.log  temp001.dbf   undotbs01.dbf
[oracle@itkaifang itkaifang]$ rm undotbs02.dbf 
[oracle@itkaifang itkaifang]$ ls
control01.ctl  redo01.log  sysaux01.dbf  temp01.dbf     users01.dbf
control02.ctl  redo02.log  system01.dbf  temp101.dbf
example01.dbf  redo03.log  temp001.dbf   undotbs01.dbf
[oracle@itkaifang itkaifang]$

2.实验验证闪回版本,针对表中的同一数据进行三次更新事物;

itkaifang@SYS> conn scott/tiger
Connected.
Session altered.
itkaifang@SCOTT> select ename,job,sal from emp where empno=7369;
ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK           1400
itkaifang@SCOTT> update emp set sal=sal-200 where empno=7369;
1 row updated.
itkaifang@SCOTT> commit;
Commit complete.
itkaifang@SCOTT> update emp set sal=sal-200 where empno=7369;
1 row updated.
itkaifang@SCOTT> commit;
Commit complete.
itkaifang@SCOTT> update emp set sal=sal-200 where empno=7369;
1 row updated.
itkaifang@SCOTT> commit;
Commit complete.
itkaifang@SCOTT>

查询10分钟内针对事物生成的版本;

itkaifang@SCOTT> select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,versions_xid,versions_startscn,versions_endscn,sal from emp versions between timestamp sysdate-10/1440 and sysdate where empno=7369;
STARTTIME           VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN        SAL
------------------- ---------------- ----------------- --------------- ----------
2017-04-23 15:37:41 02001000CE060000           2260882                        800
2017-04-23 15:37:32 04000300FC050000           2260876         2260882       1000
2017-04-23 15:36:58 050000005A070000           2260864         2260876       1200
                                                               2260864       1400
itkaifang@SCOTT>

相关文章

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

网站分类
最新文章
    随机文章
      站点信息
      • 文章总数:113
      • 页面总数:2
      • 分类总数:3
      • 标签总数:0
      • 评论总数:0
      • 浏览总数:29694
      左邻右舍

      BlogPowerBy Z-BlogPHP 1.5 Zero ;Theme By 爱墙纸

      IT开放社区:京ICP备13044647号.初创于Oracle DBA实战班.邮箱:admin@itkaifang.com

      分享:

      支付宝

      微信