UNDO自动管理和手工管理、闪回操作

已于 2022-11-24 11:31:32 修改
阅读量369 收藏
点赞数
分类专栏： DBA 文章标签： 数据库 oracle dba
于 2022-11-23 22:40:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011868279/article/details/127995144
版权

undo tablespace之下 --》使用rollback segment保存数据修改前的老镜像

rollback segment的管理方法：

SQL> 
SQL> show parameter undo_management
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
SQL> show parameter undo_tablespace
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1
SQL> 
SQL> 
SQL> 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
 
11 rows selected.
 
SQL> show con_name
 
CON_NAME
------------------------------
CDB$ROOT
SQL> 
SQL> alter session set container=PDB1;
 
Session altered.
 
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/pdb1/undotbs02.dbf' size 10m;
 
Tablespace created.
 
SQL> 
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
 
SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_3588498444$           UNDOTBS1                       ONLINE
_SYSSMU2_2971032042$           UNDOTBS1                       ONLINE
_SYSSMU3_3657342154$           UNDOTBS1                       ONLINE
_SYSSMU4_811969446$            UNDOTBS1                       ONLINE
_SYSSMU5_3018429039$           UNDOTBS1                       ONLINE
_SYSSMU6_442110264$            UNDOTBS1                       ONLINE
_SYSSMU7_2728255665$           UNDOTBS1                       ONLINE
_SYSSMU8_801938064$            UNDOTBS1                       ONLINE
_SYSSMU9_647420285$            UNDOTBS1                       ONLINE
_SYSSMU10_2262159254$          UNDOTBS1                       ONLINE
 
SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
_SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
_SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
_SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
_SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
_SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
_SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
_SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
_SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
_SYSSMU20_459963109$           UNDOTBS2                       OFFLINE
 
21 rows selected.
 
SQL> 
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/undotbs02.dbf

12 rows selected.

SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/undotbs02.dbf' size 10m;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/undotbs02.dbf
/u02/oradata/CDB1/undotbs02.dbf

13 rows selected.

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_1261223759$           UNDOTBS1                       ONLINE
_SYSSMU2_27624015$             UNDOTBS1                       ONLINE
_SYSSMU3_2421748942$           UNDOTBS1                       ONLINE
_SYSSMU4_625702278$            UNDOTBS1                       ONLINE
_SYSSMU5_2101348960$           UNDOTBS1                       ONLINE
_SYSSMU6_813816332$            UNDOTBS1                       ONLINE
_SYSSMU7_2329891355$           UNDOTBS1                       ONLINE
_SYSSMU8_399776867$            UNDOTBS1                       ONLINE
_SYSSMU9_1692468413$           UNDOTBS1                       ONLINE
_SYSSMU10_930580995$           UNDOTBS1                       ONLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_819496071$           UNDOTBS2                       OFFLINE
_SYSSMU12_3852438105$          UNDOTBS2                       OFFLINE
_SYSSMU13_4053449264$          UNDOTBS2                       OFFLINE
_SYSSMU14_3568283922$          UNDOTBS2                       OFFLINE
_SYSSMU15_4273800205$          UNDOTBS2                       OFFLINE
_SYSSMU16_3358818169$          UNDOTBS2                       OFFLINE
_SYSSMU17_1656102559$          UNDOTBS2                       OFFLINE
_SYSSMU18_3046128593$          UNDOTBS2                       OFFLINE
_SYSSMU19_2820437463$          UNDOTBS2                       OFFLINE
_SYSSMU20_1569246834$          UNDOTBS2                       OFFLINE

21 rows selected.

SQL> 

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/undotbs02.dbf

12 rows selected.

SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/undotbs02.dbf' size 10m;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/undotbs02.dbf
/u02/oradata/CDB1/undotbs02.dbf

13 rows selected.

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_1261223759$           UNDOTBS1                       ONLINE
_SYSSMU2_27624015$             UNDOTBS1                       ONLINE
_SYSSMU3_2421748942$           UNDOTBS1                       ONLINE
_SYSSMU4_625702278$            UNDOTBS1                       ONLINE
_SYSSMU5_2101348960$           UNDOTBS1                       ONLINE
_SYSSMU6_813816332$            UNDOTBS1                       ONLINE
_SYSSMU7_2329891355$           UNDOTBS1                       ONLINE
_SYSSMU8_399776867$            UNDOTBS1                       ONLINE
_SYSSMU9_1692468413$           UNDOTBS1                       ONLINE
_SYSSMU10_930580995$           UNDOTBS1                       ONLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_819496071$           UNDOTBS2                       OFFLINE
_SYSSMU12_3852438105$          UNDOTBS2                       OFFLINE
_SYSSMU13_4053449264$          UNDOTBS2                       OFFLINE
_SYSSMU14_3568283922$          UNDOTBS2                       OFFLINE
_SYSSMU15_4273800205$          UNDOTBS2                       OFFLINE
_SYSSMU16_3358818169$          UNDOTBS2                       OFFLINE
_SYSSMU17_1656102559$          UNDOTBS2                       OFFLINE
_SYSSMU18_3046128593$          UNDOTBS2                       OFFLINE
_SYSSMU19_2820437463$          UNDOTBS2                       OFFLINE
_SYSSMU20_1569246834$          UNDOTBS2                       OFFLINE

21 rows selected.

SQL> show user
USER is "SYS"
SQL> col username for a10
SQL> select s.USERNAME,t.XIDUSN,t.USED_UBLK from v$session s, v$transaction t where t.SES_ADDR=s.SADDR;

USERNAME       XIDUSN  USED_UBLK
---------- ---------- ----------
SCOTT               8         20
SCOTT               2          1

SQL> 
SQL> select name from v$rollname where usn=8;

NAME
------------------------------
_SYSSMU8_399776867$

SQL> select name from v$rollname where usn=2;

NAME
------------------------------
_SYSSMU2_27624015$

SQL>

UNDO手工管理

SQL> 
SQL> show parameter undo_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1
SQL> 
SQL> alter system set undo_management=manual scope=spfile
  2  ;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  629145392 bytes
Fixed Size                  9137968 bytes
Variable Size             184549376 bytes
Database Buffers          427819008 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> show parameter undo_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
SQL> 

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_1261223759$           UNDOTBS1                       OFFLINE
_SYSSMU2_27624015$             UNDOTBS1                       OFFLINE
_SYSSMU3_2421748942$           UNDOTBS1                       OFFLINE
_SYSSMU4_625702278$            UNDOTBS1                       OFFLINE
_SYSSMU5_2101348960$           UNDOTBS1                       OFFLINE
_SYSSMU6_813816332$            UNDOTBS1                       OFFLINE
_SYSSMU7_2329891355$           UNDOTBS1                       OFFLINE
_SYSSMU8_399776867$            UNDOTBS1                       OFFLINE
_SYSSMU9_1692468413$           UNDOTBS1                       OFFLINE
_SYSSMU10_930580995$           UNDOTBS1                       OFFLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_819496071$           UNDOTBS2                       OFFLINE
_SYSSMU12_3852438105$          UNDOTBS2                       OFFLINE
_SYSSMU13_4053449264$          UNDOTBS2                       OFFLINE
_SYSSMU14_3568283922$          UNDOTBS2                       OFFLINE
_SYSSMU15_4273800205$          UNDOTBS2                       OFFLINE
_SYSSMU16_3358818169$          UNDOTBS2                       OFFLINE
_SYSSMU17_1656102559$          UNDOTBS2                       OFFLINE
_SYSSMU18_3046128593$          UNDOTBS2                       OFFLINE
_SYSSMU19_2820437463$          UNDOTBS2                       OFFLINE
_SYSSMU20_1569246834$          UNDOTBS2                       OFFLINE

21 rows selected.

SQL>

当切换到手工管理UNDO表空间执行update语句时，遇到如下错误：

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus scott/tiger@PDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 23 11:04:26 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Nov 23 2022 10:46:24 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SCOTT"
SQL> 
SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> update e01 set sal=sal+1;
update e01 set sal=sal+1
       *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'


SQL>

将 表e01移动到系统表空间后，可以进行表更新。

SQL> 
SQL> alter table e01 move tablespace system;

Table altered.

SQL> 
SQL> update e01 set sal=sal+1;

14 rows updated.

SQL> roll
Rollback complete.
SQL> 
SQL>

在scott用户下尝试做建表实验：

SQL> 
SQL> 
SQL> select * from tab
  2  ;

TNAME                                                                                                                            TABTYPE        CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
T02                                                                                                                              TABLE
T01                                                                                                                              TABLE
T03                                                                                                                              TABLE
DEPT                                                                                                                             TABLE
EMP                                                                                                                              TABLE
BONUS                                                                                                                            TABLE
SALGRADE                                                                                                                         TABLE
DETAIL_DEPT                                                                                                                      TABLE
VU10                                                                                                                             VIEW
VUTEST                                                                                                                           VIEW
E01                                                                                                                              TABLE
TEMP                                                                                                                             TABLE
TEMP02                                                                                                                           TABLE
OB1                                                                                                                              TABLE

14 rows selected.

SQL> 
SQL> 
SQL> 
SQL> create table e02 as select * from emp;
create table e02 as select * from emp
                                  *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'


SQL> desc e02;
ERROR:
ORA-04043: object e02 does not exist


SQL> create table e02 as select * from emp where 1=0;

Table created.

SQL> desc e02;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 EMPNO                                                                                                                      NUMBER(4)
 ENAME                                                                                                                      VARCHAR2(10)
 JOB                                                                                                                        VARCHAR2(9)
 MGR                                                                                                                        NUMBER(4)
 HIREDATE                                                                                                                   DATE
 SAL                                                                                                                        NUMBER(7,2)
 COMM                                                                                                                       NUMBER(7,2)
 DEPTNO                                                                                                                     NUMBER(2)

SQL>

 结论：创建空表是可以建立的，但带数据建表是不可以的。建表能成功的原因是deferred_segment_creation处于打开状态。（延迟段创建）

SQL> show parameter segment

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
rollback_segments                    string
transactions_per_rollback_segment    integer     5
SQL>

我们尝试将这个延迟段创建关闭，看看SCOTT用户是否可以继续创建表呢？如下所示：

  • 延迟段创建关闭
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba     

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 23 15:55:34 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> show parameter segment

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
rollback_segments                    string
transactions_per_rollback_segment    integer     5
SQL> alter system set deferred_segment_creation=FALSE;

System altered.

SQL> show parameter segment

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE
rollback_segments                    string
transactions_per_rollback_segment    integer     5
SQL>

结论是可以单纯建表不带数据，但无法带数建表。

SQL> create table e02 as select * from emp;
create table e02 as select * from emp
                                  *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'


SQL> create table e03 as select * from emp where 1=0;

Table created.

SQL> select * from tab;

TNAME                                                                                                                            TABTYPE        CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
T02                                                                                                                              TABLE
T01                                                                                                                              TABLE
T03                                                                                                                              TABLE
DEPT                                                                                                                             TABLE
EMP                                                                                                                              TABLE
BONUS                                                                                                                            TABLE
SALGRADE                                                                                                                         TABLE
DETAIL_DEPT                                                                                                                      TABLE
VU10                                                                                                                             VIEW
VUTEST                                                                                                                           VIEW
E01                                                                                                                              TABLE
TEMP                                                                                                                             TABLE
TEMP02                                                                                                                           TABLE
E03                                                                                                                              TABLE
OB1                                                                                                                              TABLE

15 rows selected.

SQL>

 回滚段的手工管理：

create rollback segment rbs1 tablespace UNDOTBS1;
create rollback segment rbs2 tablespace UNDOTBS1;
create rollback segment rbs3 tablespace UNDOTBS1;

alter system set rollback_segments='RBS1'，‘RBS2’, 'RBS3' scope=spfile;

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_3588498444$           UNDOTBS1                       OFFLINE
_SYSSMU2_2971032042$           UNDOTBS1                       OFFLINE
_SYSSMU3_3657342154$           UNDOTBS1                       OFFLINE
_SYSSMU4_811969446$            UNDOTBS1                       OFFLINE
_SYSSMU5_3018429039$           UNDOTBS1                       OFFLINE
_SYSSMU6_442110264$            UNDOTBS1                       OFFLINE
_SYSSMU7_2728255665$           UNDOTBS1                       OFFLINE
_SYSSMU8_801938064$            UNDOTBS1                       OFFLINE
_SYSSMU9_647420285$            UNDOTBS1                       OFFLINE
_SYSSMU10_2262159254$          UNDOTBS1                       OFFLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
_SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
_SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
_SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
_SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
_SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
_SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
_SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
_SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
_SYSSMU20_459963109$           UNDOTBS2                       OFFLINE

21 rows selected.

SQL> create rollback segment rbs1 tablespace UNDOTBS1;

Rollback segment created.

SQL> 
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_3588498444$           UNDOTBS1                       OFFLINE
_SYSSMU2_2971032042$           UNDOTBS1                       OFFLINE
_SYSSMU3_3657342154$           UNDOTBS1                       OFFLINE
_SYSSMU4_811969446$            UNDOTBS1                       OFFLINE
_SYSSMU5_3018429039$           UNDOTBS1                       OFFLINE
_SYSSMU6_442110264$            UNDOTBS1                       OFFLINE
_SYSSMU7_2728255665$           UNDOTBS1                       OFFLINE
_SYSSMU8_801938064$            UNDOTBS1                       OFFLINE
_SYSSMU9_647420285$            UNDOTBS1                       OFFLINE
_SYSSMU10_2262159254$          UNDOTBS1                       OFFLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
RBS1                           UNDOTBS1                       OFFLINE
_SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
_SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
_SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
_SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
_SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
_SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
_SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
_SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
_SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
_SYSSMU20_459963109$           UNDOTBS2                       OFFLINE

22 rows selected.

SQL>

SQL> show parameter rollback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
rollback_segments                    string
transactions_per_rollback_segment    integer     5
SQL> 
SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_3588498444$           UNDOTBS1                       OFFLINE
_SYSSMU2_2971032042$           UNDOTBS1                       OFFLINE
_SYSSMU3_3657342154$           UNDOTBS1                       OFFLINE
_SYSSMU4_811969446$            UNDOTBS1                       OFFLINE
_SYSSMU5_3018429039$           UNDOTBS1                       OFFLINE
_SYSSMU6_442110264$            UNDOTBS1                       OFFLINE
_SYSSMU7_2728255665$           UNDOTBS1                       OFFLINE
_SYSSMU8_801938064$            UNDOTBS1                       OFFLINE
_SYSSMU9_647420285$            UNDOTBS1                       OFFLINE
_SYSSMU10_2262159254$          UNDOTBS1                       OFFLINE
RBS1                           UNDOTBS1                       OFFLINE
_SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
_SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
_SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
_SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
_SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
_SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
_SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
_SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
_SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
_SYSSMU20_459963109$           UNDOTBS2                       OFFLINE

22 rows selected.

SQL> 
SQL> 
SQL> alter system set rollback_segments='RBS1' scope=spfile;

System altered.

SQL> startup force
Pluggable Database opened.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_3588498444$           UNDOTBS1                       OFFLINE
_SYSSMU2_2971032042$           UNDOTBS1                       OFFLINE
_SYSSMU3_3657342154$           UNDOTBS1                       OFFLINE
_SYSSMU4_811969446$            UNDOTBS1                       OFFLINE
_SYSSMU5_3018429039$           UNDOTBS1                       OFFLINE
_SYSSMU6_442110264$            UNDOTBS1                       OFFLINE
_SYSSMU7_2728255665$           UNDOTBS1                       OFFLINE
_SYSSMU8_801938064$            UNDOTBS1                       OFFLINE
_SYSSMU9_647420285$            UNDOTBS1                       OFFLINE
_SYSSMU10_2262159254$          UNDOTBS1                       OFFLINE
RBS1                           UNDOTBS1                       ONLINE
_SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
_SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
_SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
_SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
_SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
_SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
_SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
_SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
_SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
_SYSSMU20_459963109$           UNDOTBS2                       OFFLINE

22 rows selected.

SQL>

 提供对DML操作的闪回处理

闪回查询：

SQL> select * from e01;

     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 24-JAN-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 02-APR-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

14 rows selected.

SQL> update e01 set sal = 1;

14 rows updated.

SQL> commit;

Commit complete.

SQL> rollback;

Rollback complete.

SQL> select * from e01;

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

14 rows selected.

SQL> select * from e01 as of timestamp(sysdate-5/1440);

     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 24-JAN-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 02-APR-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

14 rows selected.

SQL> alter table e01 enable row movement;

Table altered.

SQL> flashback table e01 to timestamp(sysdate-5/1440);

Flashback complete.

SQL> select * from e01;

     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 24-JAN-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 02-APR-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

14 rows selected.

SQL>

最多可以闪回15分钟，900秒。

SQL> select * from e01 as of timestamp(sysdate-15/1440);

     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 24-JAN-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 02-APR-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

14 rows selected.

SQL>

闪回版本查询

SQL> update e01 set sal=sal+100 where deptno=10;

3 rows updated.

SQL> commit;

Commit complete.

SQL> update e01 set sal=sal*1.1 where deptno=30;

6 rows updated.

SQL> commit;

Commit complete.

SQL> update e01 set sal=3;

14 rows updated.

SQL> commit;

Commit complete.

SQL> select * from e01;

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

14 rows selected.

SQL> --闪回版本查询
SQL> select versions_startscn,versions_endscn,versions_operation,versions_xid,sal from e01 versions between scn_minvalue and maxvalue where empno=7369;
select versions_startscn,versions_endscn,versions_operation,versions_xid,sal from e01 versions between scn_minvalue and maxvalue where empno=7369
                                                                                                       *
ERROR at line 1:
ORA-00905: missing keyword


SQL> select 
  2  versions_startscn,
  3  versions_endscn,
  4  versions_operation,
  5  versions_xid,
  6  sal 
  7  from e01 
  8  versions between scn minvalue and maxvalue 
  9  where empno=7369;

VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID            SAL
----------------- --------------- - ---------------- ----------
         17826843                 U 03000300BC030000          3
         17826652        17826843 I 070010006E030000        800
         17826652                 D 070010006E030000          1
         17825320        17826652 U 03002000BB030000          1
                         17825320                           800

SQL> 
SQL> col versions_starttime for a25;
SQL> col versions_endtime for a25;
SQL> select
  2  versions_starttime
  3  versions_endtime, 
  4  --versions_startscn,
  5  --versions_endscn,
  6  versions_operation,
  7  versions_xid,
  8  sal 
  9  from e01 
 10  versions between scn minvalue and maxvalue 
 11  where empno=7369;

VERSIONS_ENDTIME          V VERSIONS_XID            SAL
------------------------- - ---------------- ----------
23-NOV-22 09.55.07 PM     U 03000300BC030000          3
23-NOV-22 09.50.16 PM     I 070010006E030000        800
23-NOV-22 09.50.16 PM     D 070010006E030000          1
                                                      1

SQL> --闪回版本查询
SQL> select * from e01 as of scn 17826652;

     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 24-JAN-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 02-APR-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

14 rows selected.

SQL> flashback table e01 to scn 17826652;

Flashback complete.

SQL> select * from e01;

     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 24-JAN-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 02-APR-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

14 rows selected.

SQL>

注意注意： 要想做闪回表的操作，必须先开行移动,如果不打开行移动，将无法进行闪回操作。

SQL> alter table e01 enable row movement;

Table altered.

开启追加日志数据模式：

SQL> 
SQL> alter database add supplemental log data;

Database altered.

SQL> 

SQL> 
SQL> update e01 set sal=sal*1.15 where deptno=30;

6 rows updated.

SQL>

SQL> select
  2  --versions_starttime
  3  --versions_endtime, 
  4  versions_startscn,
  5  versions_endscn,
  6  versions_operation,
  7  versions_xid,
  8  sal 
  9  from e01 
 10  versions between scn minvalue and maxvalue 
 11  where empno=7844;

VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID            SAL
----------------- --------------- - ---------------- ----------
         17846142                 I 02000B0061030000       1500
         17846142                 D 02000B0061030000          3
                         17846142                             3

SQL>

闪回事务查询


select
--versions_starttime
--versions_endtime, 
versions_startscn,
versions_endscn,
versions_operation,
versions_xid,
sal 
from e01 
versions between scn minvalue and maxvalue 
where empno=7844;

---闪回事务查询，sys用户才可以查询

SQL> select undo_sql from flashback_transaction_query where xid='02000B0061030000';

UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

















16 rows selected.

SQL>

数据的串行访问：（都是从UNDO读取的数据）

设置事务隔离级来模拟数据串行访问

SQL> set transaction isolation level serializable;

Transaction set.

SQL>

画面1：

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 24 09:51:46 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn scott/tiger@PDB1;
Connected.
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> set transaction isolation level serializable;

Transaction set.

SQL> select distinct sal from e01;

       SAL
----------
         1

SQL> select distinct sal from e01;

       SAL
----------
         1

SQL>

画面2：

[oracle@oracle-db-19c ~]$ sqlplus scott/tiger@PDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 24 10:01:22 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu Nov 24 2022 09:52:09 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> 
SQL> select distinct sal from e01;

       SAL
----------
         1

SQL> update e01 set sal=2;

57344 rows updated.

SQL> select distinct sal from e01;

       SAL
----------
         2

SQL> commit;

Commit complete.

SQL> set transaction isolation level serializable;

Transaction set.

SQL> select distinct sal from e01;

       SAL
----------
         2

SQL>

回退保实例

打开回退强度保实例的作用是，老镜像不到15分钟是不允许被覆盖

SQL> set transaction isolation level serializable;

Transaction set.

SQL>

SQL> alter tablespace UNDOTBS2 RETENTION GUARANTEE;

Tablespace altered.

SQL>

SQL> select tablespace_name,retention from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME                RETENTION
------------------------------ -----------
UNDOTBS1                       NOGUARANTEE
UNDOTBS2                       NOGUARANTEE

SQL>

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 24 09:57:26 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> 
SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> select tablespace_name,retention from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME                RETENTION
------------------------------ -----------
UNDOTBS1                       NOGUARANTEE
UNDOTBS2                       NOGUARANTEE

SQL> alter tablespace UNDOTBS2 RETENTION GUARANTEE;

Tablespace altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> select tablespace_name,retention from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME                RETENTION
------------------------------ -----------
UNDOTBS1                       NOGUARANTEE
UNDOTBS2                       GUARANTEE

SQL>

恢复到原本状态：
drop tablespace UNDOTBS2 including contents and datafiles;

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP01
SYSTEM
SYSAUX
UNDOTBS1
TEMP
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEMP02
UNDOTBS2
UNDOTBS2

17 rows selected.

SQL> alter session set container=PDB1;

Session altered.

SQL> drop tablespace TEMP02 including contents and datafiles;

Tablespace dropped.

SQL> 
SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> 
SQL> 
SQL> alter system set undo_tablespace=undotbs1;

System altered.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP01
SYSTEM
SYSAUX
UNDOTBS1
TEMP
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

14 rows selected.

SQL>
DB架构
关注
专栏目录
达梦数据库管理：DMSQL基础
Sebastien23的博客
04-07 63
达梦的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。达梦数据库中空值参与排序，默认排在最前面，使用。关键字可以把空值排在最后。
【云原生进阶之数据库技术】第二章-Oracle-原理-4.3.3-段（Segment）结构剖析
最新发布
junbaozi的专栏
06-04 1226
数据库中的表在表空间中都由一块单独的磁盘空间区域，称之为段。每一段依次在表空间中有一块初始的磁盘空间区域，成为初始盘区。当段使用完对应的空间时，磁盘空间的另一个单独区域——下一盘区将被启用。如果这一块区域也被使用完，那么还有另一盘区被启用。这个过程会跟随每一个表持续执行，直到整个表空间填充满为止。数据库中的表由一个或多个的段组成，而普通表由一个段组成，分区表则由多个段组成段是由多个数据区构成的，它是为特定的数据库对象（如表段、索引段、回滚段、临时段）分配的一系列数据区。
undo管理
gumengkai的博客
04-05 1884
UNDO段是用于存储还原数据的特殊段，在发生实例故障的时候，undo段用来对数据进行恢复 一.undo段的作用   rdbms(关系型数据库管理系统)必须要提供一致性的数据，以确保不会丢失数据或导致数据混乱。 当执行insert,update,delete等dml操作时，服务器进程会将原有数据(称为undo数据)存放到undo段 中,undo段是用于存储还原数据的段。  
自动UNDO管理 一
congnianmi7492的博客
01-12 473
UNDO管理之一：自动UNDO管理-------------------------------------------------------------------------------- 实验版本 UNDO基础 UND...
Oracle UNDO - 自动管理疑问解答
05-02 1492
最近发现Oracle UNDO TUNED_UNDORETENTION持续增长 环境：RHEL5.5 x64 + Oracle 11.1.0.7.0 现状： 1，UNDO设置     SQL> show parameter undo     NAME                                 TYPE        VALUE     --------------
管理UNDO
个人公众号：Alen的数据库学习笔记
08-24 747
UNDO是什么？ Oracle数据库创建并管理被用来回滚、撤销，和对数据库更改的信息，这些信息主要由被提交前的事务行为记录组成，这些记录统称为UNDO。 UNDO有什么作用？ 当ROLLBACK语句执行时进行回滚事务； 恢复数据库； 提供读一致性； 使用闪回查询分析更早时间点的数据； 使用闪回特性恢复逻辑故障。 当ROLLBACK语句执行时，通过未提
管理undo
06-28 328
一、UNDO的自动管理与手动管理 undo推荐使用自动管理 在早期的版本中，undo使用手动管理，管理起来相当的麻烦烦琐。 要手动的创建undo的回滚段，要手动使这些回滚段上线，要手动的指定事务使用哪个回滚段。 如下例 [oracle@oel ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu May 12 ...
Oracle 12c Undo数据管理分析.pptx
10-14
2. Unexpired（未过期）：已提交的 Undo 信息，需要满足 Undo 保留期，用于读一致性查询和可能的闪回操作。 3. Expired（过期）：过期的 Undo 信息，当需要空间支持新的活跃事务时，可以被覆盖。 在实际操作中，...
本地管理表空间（LMT）与自动段空间管理（ASSM）概念
weixin_30654583的博客
02-04 578
创建表空间时，extent management local 定义本地管理表空间（LMT），segment space management auto 定义自动段空间管理（ASSM）。 extent management local（本地管理表空间）是在表空间的头部用位图区记录extent（区）的使用情况（是否为free）。与之相对的是字典管理表空间，用数据字典记录extent使用信息。 ...
Oracle Certified Professional 063 - full操作文档手册.pdf
10-19
闪回技术是Oracle数据库的一大特色，能对User error造成的错误进行快速恢复，例如通过UNDO数据或闪回日志。Oracle 11g提供了多种闪回功能，如Flashback Query、Flashback Versions Query、Flashback Transaction ...
undo表空间手动管理
czmmiao的专栏
03-25 245
UNDO_MANAGEMENT specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space i...
oracle undoautotune,关于oracle UNDO表空间自动管理与自动调优的原则(包括FAQ-Automatic Undo Management)...
weixin_39923137的博客
04-09 240
关于oracle UNDO表空间自动管理自动调优的原则介绍,在Oracle 10gr2后面的版本中添加了UNDO信息最短保留时间段自动调优的特性，不再仅仅依据参数UNDO_RETENTION的设定，其调优原则如下：1 当UNDO TABLESPACE为fixed-size，Oracle将根据表空间的大小和历史使用情况，自动调整undo信息保存时间，同时忽略undo_retention的值除非und...
利用undo快速闪回
sqlora的专栏
06-20 386
10点接到恢复需求9点的数据 SQL> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _gc_undo_affinity ...
闪回查询(找出undo sql方法)
Daniel's Blog
12-17 582
SELECT versions_xid,versions_operation, versions_starttime,versions_endtime,VERSIONS_STARTSCN,VERSIONS_ENDSCN FROM site_daily_report versions BETWEEN TIMESTAMP minvalue AND maxvalue WHERE report_
Undo管理
weixin_34015336的博客
07-14 96
本篇文章中的内容来自ORACLE 11GR2版本的《Administrator's Guide》第16章，该章主要介绍了管理Undo表空间的相关知识。建议有英文基础的同学直接去看《Administrator's Guide》。一、Undo记录的作用1.事务未提交时的回滚；2.恢复数据库3.保证数据库的读一致性；4.闪回查询、闪回事务、闪回表二、自动回滚段管理（Auto...
oracle 手工管理undo,oracle undo管理
weixin_36478866的博客
04-07 418
1.概念介绍UNDO是Oracle中的一个很重要的机制，在对数据库进行修改的时候，Oracle会将数据块上修改之前的数据(称为前映像，before image)保存在回滚段中，这样当我们需要进行回滚(rollback)的时候就很容易能从回滚段中将之前的数据取出来将数据块上面的数据还原回来。2.主要用途数据回滚(rollback)数据库恢复(data recovery) ：实例恢复，即在数据库意外宕...
Oracle undo管理详解
cuigaijiao2361的博客
11-07 221
一、What is undo Every Oracle database must have a method of maintaining information that is used ...
auto undo management
iteye_13138的博客
01-14 130
auto undo management是oracle推出代替manual rollback segment，oracle官方的文档上有介绍AUM的原理和实现方法，今天我们不再重复这些内容，我们来深入一些官方文档中点到为止的东西。 undo tablespace size 当创建undo tablespace时最少包含一个初始segment，最多包含十个初始segment，每个初始se...
监控和管理Oracle UNDO表空间的使用
qq_38264153的博客
01-24 693
对Oracle数据库UNDO表空间的监控和管理是我们日常最重要的工作之一，UNDO表空间通常都是Oracle自动化管理（通过undo_management初始化参数确定）；UNDO表空间是用于存储DML操作的前镜像数据，它是实例恢复，数据回滚，一致性查询功能的重要组件；我们常常会忽略对它的监控，这会导致UNDO表空间可能出现以下问题： 1).空间使用率100%，导致DML操作无法进行。 2).告警日志中出现大量的ORA-01555告警错误。 3).实例恢复失败，数据库无法正常打开。 一.对Oracle自动化
Oracle_undo表空间详解：自动Undo管理与创建指南
Undo表空间主要用于存储自动撤销管理(Automatic Undo Management)中的undo段，这是事务处理中的临时存储区域，用于记录事务的更改以便在需要回滚时撤销这些操作。它具有以下特性： 1. **专用性**：Undo表空间是专门...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值