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

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

    rollback segment的管理方法：

    1. SQL> 
    2. SQL> show parameter undo_management
    3.  
    4. NAME                                 TYPE        VALUE
    5. ------------------------------------ ----------- ------------------------------
    6. undo_management                      string      AUTO
    7. SQL> show parameter undo_tablespace
    8.  
    9. NAME                                 TYPE        VALUE
    10. ------------------------------------ ----------- ------------------------------
    11. undo_tablespace                      string      UNDOTBS1
    12. SQL> 
    1. SQL> 
    2. SQL> 
    3. SQL> select name from v$datafile;
    4.  
    5. NAME
    6. --------------------------------------------------------------------------------
    7. /u02/oradata/CDB1/system01.dbf
    8. /u02/oradata/CDB1/sysaux01.dbf
    9. /u02/oradata/CDB1/undotbs01.dbf
    10. /u02/oradata/CDB1/pdbseed/system01.dbf
    11. /u02/oradata/CDB1/pdbseed/sysaux01.dbf
    12. /u02/oradata/CDB1/users01.dbf
    13. /u02/oradata/CDB1/pdbseed/undotbs01.dbf
    14. /u02/oradata/CDB1/pdb1/system01.dbf
    15. /u02/oradata/CDB1/pdb1/sysaux01.dbf
    16. /u02/oradata/CDB1/pdb1/undotbs01.dbf
    17. /u02/oradata/CDB1/pdb1/users01.dbf
    18.  
    19. 11 rows selected.
    20.  
    21. SQL> show con_name
    22.  
    23. CON_NAME
    24. ------------------------------
    25. CDB$ROOT
    26. SQL> 
    27. SQL> alter session set container=PDB1;
    28.  
    29. Session altered.
    30.  
    31. SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/pdb1/undotbs02.dbf' size 10m;
    32.  
    33. Tablespace created.
    34.  
    35. SQL> 
    36. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
    37.  
    38. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    39. ------------------------------ ------------------------------ ----------------
    40. SYSTEM                         SYSTEM                         ONLINE
    41. _SYSSMU1_3588498444$           UNDOTBS1                       ONLINE
    42. _SYSSMU2_2971032042$           UNDOTBS1                       ONLINE
    43. _SYSSMU3_3657342154$           UNDOTBS1                       ONLINE
    44. _SYSSMU4_811969446$            UNDOTBS1                       ONLINE
    45. _SYSSMU5_3018429039$           UNDOTBS1                       ONLINE
    46. _SYSSMU6_442110264$            UNDOTBS1                       ONLINE
    47. _SYSSMU7_2728255665$           UNDOTBS1                       ONLINE
    48. _SYSSMU8_801938064$            UNDOTBS1                       ONLINE
    49. _SYSSMU9_647420285$            UNDOTBS1                       ONLINE
    50. _SYSSMU10_2262159254$          UNDOTBS1                       ONLINE
    51.  
    52. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    53. ------------------------------ ------------------------------ ----------------
    54. _SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
    55. _SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
    56. _SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
    57. _SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
    58. _SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
    59. _SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
    60. _SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
    61. _SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
    62. _SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
    63. _SYSSMU20_459963109$           UNDOTBS2                       OFFLINE
    64.  
    65. 21 rows selected.
    66.  
    67. SQL> 
    1. SQL> select name from v$datafile;
    2.  
    3. NAME
    4. --------------------------------------------------------------------------------
    5. /u02/oradata/CDB1/system01.dbf
    6. /u02/oradata/CDB1/sysaux01.dbf
    7. /u02/oradata/CDB1/undotbs01.dbf
    8. /u02/oradata/CDB1/pdbseed/system01.dbf
    9. /u02/oradata/CDB1/pdbseed/sysaux01.dbf
    10. /u02/oradata/CDB1/users01.dbf
    11. /u02/oradata/CDB1/pdbseed/undotbs01.dbf
    12. /u02/oradata/CDB1/pdb1/system01.dbf
    13. /u02/oradata/CDB1/pdb1/sysaux01.dbf
    14. /u02/oradata/CDB1/pdb1/undotbs01.dbf
    15. /u02/oradata/CDB1/pdb1/users01.dbf
    16.  
    17. NAME
    18. --------------------------------------------------------------------------------
    19. /u02/oradata/CDB1/pdb1/undotbs02.dbf
    20.  
    21. 12 rows selected.
    22.  
    23. SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/undotbs02.dbf' size 10m;
    24.  
    25. Tablespace created.
    26.  
    27. SQL> select name from v$datafile;
    28.  
    29. NAME
    30. --------------------------------------------------------------------------------
    31. /u02/oradata/CDB1/system01.dbf
    32. /u02/oradata/CDB1/sysaux01.dbf
    33. /u02/oradata/CDB1/undotbs01.dbf
    34. /u02/oradata/CDB1/pdbseed/system01.dbf
    35. /u02/oradata/CDB1/pdbseed/sysaux01.dbf
    36. /u02/oradata/CDB1/users01.dbf
    37. /u02/oradata/CDB1/pdbseed/undotbs01.dbf
    38. /u02/oradata/CDB1/pdb1/system01.dbf
    39. /u02/oradata/CDB1/pdb1/sysaux01.dbf
    40. /u02/oradata/CDB1/pdb1/undotbs01.dbf
    41. /u02/oradata/CDB1/pdb1/users01.dbf
    42.  
    43. NAME
    44. --------------------------------------------------------------------------------
    45. /u02/oradata/CDB1/pdb1/undotbs02.dbf
    46. /u02/oradata/CDB1/undotbs02.dbf
    47.  
    48. 13 rows selected.
    49.  
    50. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
    51.  
    52. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    53. ------------------------------ ------------------------------ ----------------
    54. SYSTEM                         SYSTEM                         ONLINE
    55. _SYSSMU1_1261223759$           UNDOTBS1                       ONLINE
    56. _SYSSMU2_27624015$             UNDOTBS1                       ONLINE
    57. _SYSSMU3_2421748942$           UNDOTBS1                       ONLINE
    58. _SYSSMU4_625702278$            UNDOTBS1                       ONLINE
    59. _SYSSMU5_2101348960$           UNDOTBS1                       ONLINE
    60. _SYSSMU6_813816332$            UNDOTBS1                       ONLINE
    61. _SYSSMU7_2329891355$           UNDOTBS1                       ONLINE
    62. _SYSSMU8_399776867$            UNDOTBS1                       ONLINE
    63. _SYSSMU9_1692468413$           UNDOTBS1                       ONLINE
    64. _SYSSMU10_930580995$           UNDOTBS1                       ONLINE
    65.  
    66. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    67. ------------------------------ ------------------------------ ----------------
    68. _SYSSMU11_819496071$           UNDOTBS2                       OFFLINE
    69. _SYSSMU12_3852438105$          UNDOTBS2                       OFFLINE
    70. _SYSSMU13_4053449264$          UNDOTBS2                       OFFLINE
    71. _SYSSMU14_3568283922$          UNDOTBS2                       OFFLINE
    72. _SYSSMU15_4273800205$          UNDOTBS2                       OFFLINE
    73. _SYSSMU16_3358818169$          UNDOTBS2                       OFFLINE
    74. _SYSSMU17_1656102559$          UNDOTBS2                       OFFLINE
    75. _SYSSMU18_3046128593$          UNDOTBS2                       OFFLINE
    76. _SYSSMU19_2820437463$          UNDOTBS2                       OFFLINE
    77. _SYSSMU20_1569246834$          UNDOTBS2                       OFFLINE
    78.  
    79. 21 rows selected.
    80.  
    81. SQL> 

    1. SQL> select name from v$datafile;
    2.  
    3. NAME
    4. --------------------------------------------------------------------------------
    5. /u02/oradata/CDB1/system01.dbf
    6. /u02/oradata/CDB1/sysaux01.dbf
    7. /u02/oradata/CDB1/undotbs01.dbf
    8. /u02/oradata/CDB1/pdbseed/system01.dbf
    9. /u02/oradata/CDB1/pdbseed/sysaux01.dbf
    10. /u02/oradata/CDB1/users01.dbf
    11. /u02/oradata/CDB1/pdbseed/undotbs01.dbf
    12. /u02/oradata/CDB1/pdb1/system01.dbf
    13. /u02/oradata/CDB1/pdb1/sysaux01.dbf
    14. /u02/oradata/CDB1/pdb1/undotbs01.dbf
    15. /u02/oradata/CDB1/pdb1/users01.dbf
    16.  
    17. NAME
    18. --------------------------------------------------------------------------------
    19. /u02/oradata/CDB1/pdb1/undotbs02.dbf
    20.  
    21. 12 rows selected.
    22.  
    23. SQL> create undo tablespace undotbs2 datafile '/u02/oradata/CDB1/undotbs02.dbf' size 10m;
    24.  
    25. Tablespace created.
    26.  
    27. SQL> select name from v$datafile;
    28.  
    29. NAME
    30. --------------------------------------------------------------------------------
    31. /u02/oradata/CDB1/system01.dbf
    32. /u02/oradata/CDB1/sysaux01.dbf
    33. /u02/oradata/CDB1/undotbs01.dbf
    34. /u02/oradata/CDB1/pdbseed/system01.dbf
    35. /u02/oradata/CDB1/pdbseed/sysaux01.dbf
    36. /u02/oradata/CDB1/users01.dbf
    37. /u02/oradata/CDB1/pdbseed/undotbs01.dbf
    38. /u02/oradata/CDB1/pdb1/system01.dbf
    39. /u02/oradata/CDB1/pdb1/sysaux01.dbf
    40. /u02/oradata/CDB1/pdb1/undotbs01.dbf
    41. /u02/oradata/CDB1/pdb1/users01.dbf
    42.  
    43. NAME
    44. --------------------------------------------------------------------------------
    45. /u02/oradata/CDB1/pdb1/undotbs02.dbf
    46. /u02/oradata/CDB1/undotbs02.dbf
    47.  
    48. 13 rows selected.
    49.  
    50. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
    51.  
    52. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    53. ------------------------------ ------------------------------ ----------------
    54. SYSTEM                         SYSTEM                         ONLINE
    55. _SYSSMU1_1261223759$           UNDOTBS1                       ONLINE
    56. _SYSSMU2_27624015$             UNDOTBS1                       ONLINE
    57. _SYSSMU3_2421748942$           UNDOTBS1                       ONLINE
    58. _SYSSMU4_625702278$            UNDOTBS1                       ONLINE
    59. _SYSSMU5_2101348960$           UNDOTBS1                       ONLINE
    60. _SYSSMU6_813816332$            UNDOTBS1                       ONLINE
    61. _SYSSMU7_2329891355$           UNDOTBS1                       ONLINE
    62. _SYSSMU8_399776867$            UNDOTBS1                       ONLINE
    63. _SYSSMU9_1692468413$           UNDOTBS1                       ONLINE
    64. _SYSSMU10_930580995$           UNDOTBS1                       ONLINE
    65.  
    66. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    67. ------------------------------ ------------------------------ ----------------
    68. _SYSSMU11_819496071$           UNDOTBS2                       OFFLINE
    69. _SYSSMU12_3852438105$          UNDOTBS2                       OFFLINE
    70. _SYSSMU13_4053449264$          UNDOTBS2                       OFFLINE
    71. _SYSSMU14_3568283922$          UNDOTBS2                       OFFLINE
    72. _SYSSMU15_4273800205$          UNDOTBS2                       OFFLINE
    73. _SYSSMU16_3358818169$          UNDOTBS2                       OFFLINE
    74. _SYSSMU17_1656102559$          UNDOTBS2                       OFFLINE
    75. _SYSSMU18_3046128593$          UNDOTBS2                       OFFLINE
    76. _SYSSMU19_2820437463$          UNDOTBS2                       OFFLINE
    77. _SYSSMU20_1569246834$          UNDOTBS2                       OFFLINE
    78.  
    79. 21 rows selected.
    80.  
    81. SQL> show user
    82. USER is "SYS"
    83. SQL> col username for a10
    84. SQL> select s.USERNAME,t.XIDUSN,t.USED_UBLK from v$session s, v$transaction t where t.SES_ADDR=s.SADDR;
    85.  
    86. USERNAME       XIDUSN  USED_UBLK
    87. ---------- ---------- ----------
    88. SCOTT               8         20
    89. SCOTT               2          1
    90.  
    91. SQL> 
    92. SQL> select name from v$rollname where usn=8;
    93.  
    94. NAME
    95. ------------------------------
    96. _SYSSMU8_399776867$
    97.  
    98. SQL> select name from v$rollname where usn=2;
    99.  
    100. NAME
    101. ------------------------------
    102. _SYSSMU2_27624015$
    103.  
    104. SQL>

    UNDO手工管理

    1. SQL> 
    2. SQL> show parameter undo_management
    3.  
    4. NAME                                 TYPE        VALUE
    5. ------------------------------------ ----------- ------------------------------
    6. undo_management                      string      AUTO
    7. SQL> show parameter undo_tablespace
    8.  
    9. NAME                                 TYPE        VALUE
    10. ------------------------------------ ----------- ------------------------------
    11. undo_tablespace                      string      UNDOTBS1
    12. SQL> 
    13. SQL> alter system set undo_management=manual scope=spfile
    14.   2  ;
    15.  
    16. System altered.
    17.  
    18. SQL> shutdown immediate
    19. Database closed.
    20. Database dismounted.
    21. ORACLE instance shut down.
    22. SQL> 
    23. SQL> startup
    24. ORACLE instance started.
    25.  
    26. Total System Global Area  629145392 bytes
    27. Fixed Size                  9137968 bytes
    28. Variable Size             184549376 bytes
    29. Database Buffers          427819008 bytes
    30. Redo Buffers                7639040 bytes
    31. Database mounted.
    32. Database opened.
    33. SQL> show parameter undo_management
    34.  
    35. NAME                                 TYPE        VALUE
    36. ------------------------------------ ----------- ------------------------------
    37. undo_management                      string      MANUAL
    38. SQL> 
    39.  
    40. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
    41.  
    42. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    43. ------------------------------ ------------------------------ ----------------
    44. SYSTEM                         SYSTEM                         ONLINE
    45. _SYSSMU1_1261223759$           UNDOTBS1                       OFFLINE
    46. _SYSSMU2_27624015$             UNDOTBS1                       OFFLINE
    47. _SYSSMU3_2421748942$           UNDOTBS1                       OFFLINE
    48. _SYSSMU4_625702278$            UNDOTBS1                       OFFLINE
    49. _SYSSMU5_2101348960$           UNDOTBS1                       OFFLINE
    50. _SYSSMU6_813816332$            UNDOTBS1                       OFFLINE
    51. _SYSSMU7_2329891355$           UNDOTBS1                       OFFLINE
    52. _SYSSMU8_399776867$            UNDOTBS1                       OFFLINE
    53. _SYSSMU9_1692468413$           UNDOTBS1                       OFFLINE
    54. _SYSSMU10_930580995$           UNDOTBS1                       OFFLINE
    55.  
    56. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    57. ------------------------------ ------------------------------ ----------------
    58. _SYSSMU11_819496071$           UNDOTBS2                       OFFLINE
    59. _SYSSMU12_3852438105$          UNDOTBS2                       OFFLINE
    60. _SYSSMU13_4053449264$          UNDOTBS2                       OFFLINE
    61. _SYSSMU14_3568283922$          UNDOTBS2                       OFFLINE
    62. _SYSSMU15_4273800205$          UNDOTBS2                       OFFLINE
    63. _SYSSMU16_3358818169$          UNDOTBS2                       OFFLINE
    64. _SYSSMU17_1656102559$          UNDOTBS2                       OFFLINE
    65. _SYSSMU18_3046128593$          UNDOTBS2                       OFFLINE
    66. _SYSSMU19_2820437463$          UNDOTBS2                       OFFLINE
    67. _SYSSMU20_1569246834$          UNDOTBS2                       OFFLINE
    68.  
    69. 21 rows selected.
    70.  
    71. SQL>

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

    1. [oracle@oracle-db-19c ~]$ 
    2. [oracle@oracle-db-19c ~]$ sqlplus scott/tiger@PDB1
    3.  
    4. SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 23 11:04:26 2022
    5. Version 19.3.0.0.0
    6.  
    7. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    8.  
    9. Last Successful login time: Wed Nov 23 2022 10:46:24 +08:00
    10.  
    11. Connected to:
    12. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    13. Version 19.3.0.0.0
    14.  
    15. SQL> show user
    16. USER is "SCOTT"
    17. SQL> 
    18. SQL> set pagesize 200
    19. SQL> set linesize 200
    20. SQL> 
    21. SQL> update e01 set sal=sal+1;
    22. update e01 set sal=sal+1
    23.        *
    24. ERROR at line 1:
    25. ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
    26.  
    27.  
    28. SQL>

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

    1. SQL> 
    2. SQL> alter table e01 move tablespace system;
    3.  
    4. Table altered.
    5.  
    6. SQL> 
    7. SQL> update e01 set sal=sal+1;
    8.  
    9. 14 rows updated.
    10.  
    11. SQL> roll
    12. Rollback complete.
    13. SQL> 
    14. SQL>

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

    1. SQL> 
    2. SQL> 
    3. SQL> select * from tab
    4.   2  ;
    5.  
    6. TNAME                                                                                                                            TABTYPE        CLUSTERID
    7. -------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
    8. T02                                                                                                                              TABLE
    9. T01                                                                                                                              TABLE
    10. T03                                                                                                                              TABLE
    11. DEPT                                                                                                                             TABLE
    12. EMP                                                                                                                              TABLE
    13. BONUS                                                                                                                            TABLE
    14. SALGRADE                                                                                                                         TABLE
    15. DETAIL_DEPT                                                                                                                      TABLE
    16. VU10                                                                                                                             VIEW
    17. VUTEST                                                                                                                           VIEW
    18. E01                                                                                                                              TABLE
    19. TEMP                                                                                                                             TABLE
    20. TEMP02                                                                                                                           TABLE
    21. OB1                                                                                                                              TABLE
    22.  
    23. 14 rows selected.
    24.  
    25. SQL> 
    26. SQL> 
    27. SQL> 
    28. SQL> create table e02 as select * from emp;
    29. create table e02 as select * from emp
    30.                                   *
    31. ERROR at line 1:
    32. ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
    33.  
    34.  
    35. SQL> desc e02;
    36. ERROR:
    37. ORA-04043: object e02 does not exist
    38.  
    39.  
    40. SQL> create table e02 as select * from emp where 1=0;
    41.  
    42. Table created.
    43.  
    44. SQL> desc e02;
    45.  Name                                                                                                              Null?    Type
    46.  ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
    47.  EMPNO                                                                                                                      NUMBER(4)
    48.  ENAME                                                                                                                      VARCHAR2(10)
    49.  JOB                                                                                                                        VARCHAR2(9)
    50.  MGR                                                                                                                        NUMBER(4)
    51.  HIREDATE                                                                                                                   DATE
    52.  SAL                                                                                                                        NUMBER(7,2)
    53.  COMM                                                                                                                       NUMBER(7,2)
    54.  DEPTNO                                                                                                                     NUMBER(2)
    55.  
    56. 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用户是否可以继续创建表呢？如下所示：

    • 延迟段创建关闭
    1. [oracle@oracle-db-19c ~]$ sqlplus / as sysdba     
    2.  
    3. SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 23 15:55:34 2022
    4. Version 19.3.0.0.0
    5.  
    6. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    7.  
    8.  
    9. Connected to:
    10. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    11. Version 19.3.0.0.0
    12.  
    13. SQL> alter session set container=PDB1;
    14.  
    15. Session altered.
    16.  
    17. SQL> show con_name;
    18.  
    19. CON_NAME
    20. ------------------------------
    21. PDB1
    22. SQL> show parameter segment
    23.  
    24. NAME                                 TYPE        VALUE
    25. ------------------------------------ ----------- ------------------------------
    26. deferred_segment_creation            boolean     TRUE
    27. rollback_segments                    string
    28. transactions_per_rollback_segment    integer     5
    29. SQL> alter system set deferred_segment_creation=FALSE;
    30.  
    31. System altered.
    32.  
    33. SQL> show parameter segment
    34.  
    35. NAME                                 TYPE        VALUE
    36. ------------------------------------ ----------- ------------------------------
    37. deferred_segment_creation            boolean     FALSE
    38. rollback_segments                    string
    39. transactions_per_rollback_segment    integer     5
    40. SQL>

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

    1. SQL> create table e02 as select * from emp;
    2. create table e02 as select * from emp
    3.                                   *
    4. ERROR at line 1:
    5. ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
    6.  
    7.  
    8. SQL> create table e03 as select * from emp where 1=0;
    9.  
    10. Table created.
    11.  
    12. SQL> select * from tab;
    13.  
    14. TNAME                                                                                                                            TABTYPE        CLUSTERID
    15. -------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
    16. T02                                                                                                                              TABLE
    17. T01                                                                                                                              TABLE
    18. T03                                                                                                                              TABLE
    19. DEPT                                                                                                                             TABLE
    20. EMP                                                                                                                              TABLE
    21. BONUS                                                                                                                            TABLE
    22. SALGRADE                                                                                                                         TABLE
    23. DETAIL_DEPT                                                                                                                      TABLE
    24. VU10                                                                                                                             VIEW
    25. VUTEST                                                                                                                           VIEW
    26. E01                                                                                                                              TABLE
    27. TEMP                                                                                                                             TABLE
    28. TEMP02                                                                                                                           TABLE
    29. E03                                                                                                                              TABLE
    30. OB1                                                                                                                              TABLE
    31.  
    32. 15 rows selected.
    33.  
    34. 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;

    1. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
    2.  
    3. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    4. ------------------------------ ------------------------------ ----------------
    5. SYSTEM                         SYSTEM                         ONLINE
    6. _SYSSMU1_3588498444$           UNDOTBS1                       OFFLINE
    7. _SYSSMU2_2971032042$           UNDOTBS1                       OFFLINE
    8. _SYSSMU3_3657342154$           UNDOTBS1                       OFFLINE
    9. _SYSSMU4_811969446$            UNDOTBS1                       OFFLINE
    10. _SYSSMU5_3018429039$           UNDOTBS1                       OFFLINE
    11. _SYSSMU6_442110264$            UNDOTBS1                       OFFLINE
    12. _SYSSMU7_2728255665$           UNDOTBS1                       OFFLINE
    13. _SYSSMU8_801938064$            UNDOTBS1                       OFFLINE
    14. _SYSSMU9_647420285$            UNDOTBS1                       OFFLINE
    15. _SYSSMU10_2262159254$          UNDOTBS1                       OFFLINE
    16.  
    17. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    18. ------------------------------ ------------------------------ ----------------
    19. _SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
    20. _SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
    21. _SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
    22. _SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
    23. _SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
    24. _SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
    25. _SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
    26. _SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
    27. _SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
    28. _SYSSMU20_459963109$           UNDOTBS2                       OFFLINE
    29.  
    30. 21 rows selected.
    31.  
    32. SQL> create rollback segment rbs1 tablespace UNDOTBS1;
    33.  
    34. Rollback segment created.
    35.  
    36. SQL> 
    37. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
    38.  
    39. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    40. ------------------------------ ------------------------------ ----------------
    41. SYSTEM                         SYSTEM                         ONLINE
    42. _SYSSMU1_3588498444$           UNDOTBS1                       OFFLINE
    43. _SYSSMU2_2971032042$           UNDOTBS1                       OFFLINE
    44. _SYSSMU3_3657342154$           UNDOTBS1                       OFFLINE
    45. _SYSSMU4_811969446$            UNDOTBS1                       OFFLINE
    46. _SYSSMU5_3018429039$           UNDOTBS1                       OFFLINE
    47. _SYSSMU6_442110264$            UNDOTBS1                       OFFLINE
    48. _SYSSMU7_2728255665$           UNDOTBS1                       OFFLINE
    49. _SYSSMU8_801938064$            UNDOTBS1                       OFFLINE
    50. _SYSSMU9_647420285$            UNDOTBS1                       OFFLINE
    51. _SYSSMU10_2262159254$          UNDOTBS1                       OFFLINE
    52.  
    53. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    54. ------------------------------ ------------------------------ ----------------
    55. RBS1                           UNDOTBS1                       OFFLINE
    56. _SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
    57. _SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
    58. _SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
    59. _SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
    60. _SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
    61. _SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
    62. _SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
    63. _SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
    64. _SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
    65. _SYSSMU20_459963109$           UNDOTBS2                       OFFLINE
    66.  
    67. 22 rows selected.
    68.  
    69. SQL>

    1. SQL> show parameter rollback
    2.  
    3. NAME                                 TYPE        VALUE
    4. ------------------------------------ ----------- ------------------------------
    5. fast_start_parallel_rollback         string      LOW
    6. rollback_segments                    string
    7. transactions_per_rollback_segment    integer     5
    8. SQL> 
    9. SQL> shutdown immediate
    10. Pluggable Database closed.
    11. SQL> startup
    12. Pluggable Database opened.
    13. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
    14.  
    15. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    16. ------------------------------ ------------------------------ ----------------
    17. SYSTEM                         SYSTEM                         ONLINE
    18. _SYSSMU1_3588498444$           UNDOTBS1                       OFFLINE
    19. _SYSSMU2_2971032042$           UNDOTBS1                       OFFLINE
    20. _SYSSMU3_3657342154$           UNDOTBS1                       OFFLINE
    21. _SYSSMU4_811969446$            UNDOTBS1                       OFFLINE
    22. _SYSSMU5_3018429039$           UNDOTBS1                       OFFLINE
    23. _SYSSMU6_442110264$            UNDOTBS1                       OFFLINE
    24. _SYSSMU7_2728255665$           UNDOTBS1                       OFFLINE
    25. _SYSSMU8_801938064$            UNDOTBS1                       OFFLINE
    26. _SYSSMU9_647420285$            UNDOTBS1                       OFFLINE
    27. _SYSSMU10_2262159254$          UNDOTBS1                       OFFLINE
    28. RBS1                           UNDOTBS1                       OFFLINE
    29. _SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
    30. _SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
    31. _SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
    32. _SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
    33. _SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
    34. _SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
    35. _SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
    36. _SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
    37. _SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
    38. _SYSSMU20_459963109$           UNDOTBS2                       OFFLINE
    39.  
    40. 22 rows selected.
    41.  
    42. SQL> 
    43. SQL> 
    44. SQL> alter system set rollback_segments='RBS1' scope=spfile;
    45.  
    46. System altered.
    47.  
    48. SQL> startup force
    49. Pluggable Database opened.
    50. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
    51.  
    52. SEGMENT_NAME                   TABLESPACE_NAME                STATUS
    53. ------------------------------ ------------------------------ ----------------
    54. SYSTEM                         SYSTEM                         ONLINE
    55. _SYSSMU1_3588498444$           UNDOTBS1                       OFFLINE
    56. _SYSSMU2_2971032042$           UNDOTBS1                       OFFLINE
    57. _SYSSMU3_3657342154$           UNDOTBS1                       OFFLINE
    58. _SYSSMU4_811969446$            UNDOTBS1                       OFFLINE
    59. _SYSSMU5_3018429039$           UNDOTBS1                       OFFLINE
    60. _SYSSMU6_442110264$            UNDOTBS1                       OFFLINE
    61. _SYSSMU7_2728255665$           UNDOTBS1                       OFFLINE
    62. _SYSSMU8_801938064$            UNDOTBS1                       OFFLINE
    63. _SYSSMU9_647420285$            UNDOTBS1                       OFFLINE
    64. _SYSSMU10_2262159254$          UNDOTBS1                       OFFLINE
    65. RBS1                           UNDOTBS1                       ONLINE
    66. _SYSSMU11_2950309372$          UNDOTBS2                       OFFLINE
    67. _SYSSMU12_3550765581$          UNDOTBS2                       OFFLINE
    68. _SYSSMU13_2003268147$          UNDOTBS2                       OFFLINE
    69. _SYSSMU14_200385032$           UNDOTBS2                       OFFLINE
    70. _SYSSMU15_2315478656$          UNDOTBS2                       OFFLINE
    71. _SYSSMU16_3694721358$          UNDOTBS2                       OFFLINE
    72. _SYSSMU17_302315325$           UNDOTBS2                       OFFLINE
    73. _SYSSMU18_2589589835$          UNDOTBS2                       OFFLINE
    74. _SYSSMU19_2290367767$          UNDOTBS2                       OFFLINE
    75. _SYSSMU20_459963109$           UNDOTBS2                       OFFLINE
    76.  
    77. 22 rows selected.
    78.  
    79. SQL>

     提供对DML操作的闪回处理

    闪回查询：

    1. SQL> select * from e01;
    2.  
    3.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    4. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    5.       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
    6.       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
    7.       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
    8.       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
    9.       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
    10.       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
    11.       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
    12.       7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
    13.       7839 KING       PRESIDENT            17-NOV-81       5000                    10
    14.       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
    15.       7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
    16.       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
    17.       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
    18.       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    19.  
    20. 14 rows selected.
    21.  
    22. SQL> update e01 set sal = 1;
    23.  
    24. 14 rows updated.
    25.  
    26. SQL> commit;
    27.  
    28. Commit complete.
    29.  
    30. SQL> rollback;
    31.  
    32. Rollback complete.
    33.  
    34. SQL> select * from e01;
    35.  
    36.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    37. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    38.       7369 SMITH      CLERK           7902 17-DEC-80          1                    20
    39.       7499 ALLEN      SALESMAN        7698 20-FEB-81          1        300         30
    40.       7521 WARD       SALESMAN        7698 22-FEB-81          1        500         30
    41.       7566 JONES      MANAGER         7839 02-APR-81          1                    20
    42.       7654 MARTIN     SALESMAN        7698 28-SEP-81          1       1400         30
    43.       7698 BLAKE      MANAGER         7839 01-MAY-81          1                    30
    44.       7782 CLARK      MANAGER         7839 09-JUN-81          1                    10
    45.       7788 SCOTT      ANALYST         7566 24-JAN-87          1                    20
    46.       7839 KING       PRESIDENT            17-NOV-81          1                    10
    47.       7844 TURNER     SALESMAN        7698 08-SEP-81          1          0         30
    48.       7876 ADAMS      CLERK           7788 02-APR-87          1                    20
    49.       7900 JAMES      CLERK           7698 03-DEC-81          1                    30
    50.       7902 FORD       ANALYST         7566 03-DEC-81          1                    20
    51.       7934 MILLER     CLERK           7782 23-JAN-82          1                    10
    52.  
    53. 14 rows selected.
    54.  
    55. SQL> select * from e01 as of timestamp(sysdate-5/1440);
    56.  
    57.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    58. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    59.       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
    60.       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
    61.       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
    62.       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
    63.       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
    64.       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
    65.       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
    66.       7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
    67.       7839 KING       PRESIDENT            17-NOV-81       5000                    10
    68.       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
    69.       7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
    70.       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
    71.       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
    72.       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    73.  
    74. 14 rows selected.
    75.  
    76. SQL> alter table e01 enable row movement;
    77.  
    78. Table altered.
    79.  
    80. SQL> flashback table e01 to timestamp(sysdate-5/1440);
    81.  
    82. Flashback complete.
    83.  
    84. SQL> select * from e01;
    85.  
    86.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    87. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    88.       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
    89.       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
    90.       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
    91.       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
    92.       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
    93.       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
    94.       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
    95.       7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
    96.       7839 KING       PRESIDENT            17-NOV-81       5000                    10
    97.       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
    98.       7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
    99.       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
    100.       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
    101.       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    102.  
    103. 14 rows selected.
    104.  
    105. SQL>

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

    1. SQL> select * from e01 as of timestamp(sysdate-15/1440);
    2.  
    3.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    4. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    5.       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
    6.       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
    7.       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
    8.       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
    9.       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
    10.       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
    11.       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
    12.       7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
    13.       7839 KING       PRESIDENT            17-NOV-81       5000                    10
    14.       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
    15.       7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
    16.       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
    17.       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
    18.       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    19.  
    20. 14 rows selected.
    21.  
    22. SQL>

    闪回版本查询

    1. SQL> update e01 set sal=sal+100 where deptno=10;
    2.  
    3. 3 rows updated.
    4.  
    5. SQL> commit;
    6.  
    7. Commit complete.
    8.  
    9. SQL> update e01 set sal=sal*1.1 where deptno=30;
    10.  
    11. 6 rows updated.
    12.  
    13. SQL> commit;
    14.  
    15. Commit complete.
    16.  
    17. SQL> update e01 set sal=3;
    18.  
    19. 14 rows updated.
    20.  
    21. SQL> commit;
    22.  
    23. Commit complete.
    24.  
    25. SQL> select * from e01;
    26.  
    27.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    28. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    29.       7369 SMITH      CLERK           7902 17-DEC-80          3                    20
    30.       7499 ALLEN      SALESMAN        7698 20-FEB-81          3        300         30
    31.       7521 WARD       SALESMAN        7698 22-FEB-81          3        500         30
    32.       7566 JONES      MANAGER         7839 02-APR-81          3                    20
    33.       7654 MARTIN     SALESMAN        7698 28-SEP-81          3       1400         30
    34.       7698 BLAKE      MANAGER         7839 01-MAY-81          3                    30
    35.       7782 CLARK      MANAGER         7839 09-JUN-81          3                    10
    36.       7788 SCOTT      ANALYST         7566 24-JAN-87          3                    20
    37.       7839 KING       PRESIDENT            17-NOV-81          3                    10
    38.       7844 TURNER     SALESMAN        7698 08-SEP-81          3          0         30
    39.       7876 ADAMS      CLERK           7788 02-APR-87          3                    20
    40.       7900 JAMES      CLERK           7698 03-DEC-81          3                    30
    41.       7902 FORD       ANALYST         7566 03-DEC-81          3                    20
    42.       7934 MILLER     CLERK           7782 23-JAN-82          3                    10
    43.  
    44. 14 rows selected.
    45.  
    46. SQL> --闪回版本查询
    47. SQL> select versions_startscn,versions_endscn,versions_operation,versions_xid,sal from e01 versions between scn_minvalue and maxvalue where empno=7369;
    48. select versions_startscn,versions_endscn,versions_operation,versions_xid,sal from e01 versions between scn_minvalue and maxvalue where empno=7369
    49.                                                                                                        *
    50. ERROR at line 1:
    51. ORA-00905: missing keyword
    52.  
    53.  
    54. SQL> select 
    55.   2  versions_startscn,
    56.   3  versions_endscn,
    57.   4  versions_operation,
    58.   5  versions_xid,
    59.   6  sal 
    60.   7  from e01 
    61.   8  versions between scn minvalue and maxvalue 
    62.   9  where empno=7369;
    63.  
    64. VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID            SAL
    65. ----------------- --------------- - ---------------- ----------
    66.          17826843                 U 03000300BC030000          3
    67.          17826652        17826843 I 070010006E030000        800
    68.          17826652                 D 070010006E030000          1
    69.          17825320        17826652 U 03002000BB030000          1
    70.                          17825320                           800
    71.  
    72. SQL> 
    73. SQL> col versions_starttime for a25;
    74. SQL> col versions_endtime for a25;
    75. SQL> select
    76.   2  versions_starttime
    77.   3  versions_endtime, 
    78.   4  --versions_startscn,
    79.   5  --versions_endscn,
    80.   6  versions_operation,
    81.   7  versions_xid,
    82.   8  sal 
    83.   9  from e01 
    84.  10  versions between scn minvalue and maxvalue 
    85.  11  where empno=7369;
    86.  
    87. VERSIONS_ENDTIME          V VERSIONS_XID            SAL
    88. ------------------------- - ---------------- ----------
    89. 23-NOV-22 09.55.07 PM     U 03000300BC030000          3
    90. 23-NOV-22 09.50.16 PM     I 070010006E030000        800
    91. 23-NOV-22 09.50.16 PM     D 070010006E030000          1
    92.                                                       1
    93.  
    94. SQL> --闪回版本查询
    95. SQL> select * from e01 as of scn 17826652;
    96.  
    97.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    98. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    99.       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
    100.       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
    101.       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
    102.       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
    103.       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
    104.       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
    105.       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
    106.       7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
    107.       7839 KING       PRESIDENT            17-NOV-81       5000                    10
    108.       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
    109.       7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
    110.       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
    111.       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
    112.       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    113.  
    114. 14 rows selected.
    115.  
    116. SQL> flashback table e01 to scn 17826652;
    117.  
    118. Flashback complete.
    119.  
    120. SQL> select * from e01;
    121.  
    122.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    123. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    124.       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
    125.       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
    126.       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
    127.       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
    128.       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
    129.       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
    130.       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
    131.       7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
    132.       7839 KING       PRESIDENT            17-NOV-81       5000                    10
    133.       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
    134.       7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
    135.       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
    136.       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
    137.       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    138.  
    139. 14 rows selected.
    140.  
    141. SQL>

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

    1. SQL> alter table e01 enable row movement;
    2.  
    3. Table altered.

    开启追加日志数据模式：

    1. SQL> 
    2. SQL> alter database add supplemental log data;
    3.  
    4. Database altered.
    5.  
    6. SQL> 
    1. SQL> 
    2. SQL> update e01 set sal=sal*1.15 where deptno=30;
    3.  
    4. 6 rows updated.
    5.  
    6. SQL>
    7.  
    8. SQL> select
    9.   2  --versions_starttime
    10.   3  --versions_endtime, 
    11.   4  versions_startscn,
    12.   5  versions_endscn,
    13.   6  versions_operation,
    14.   7  versions_xid,
    15.   8  sal 
    16.   9  from e01 
    17.  10  versions between scn minvalue and maxvalue 
    18.  11  where empno=7844;
    19.  
    20. VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID            SAL
    21. ----------------- --------------- - ---------------- ----------
    22.          17846142                 I 02000B0061030000       1500
    23.          17846142                 D 02000B0061030000          3
    24.                          17846142                             3
    25.  
    26. SQL> 
    27.

    闪回事务查询

    1.  
    2. select
    3. --versions_starttime
    4. --versions_endtime, 
    5. versions_startscn,
    6. versions_endscn,
    7. versions_operation,
    8. versions_xid,
    9. sal 
    10. from e01 
    11. versions between scn minvalue and maxvalue 
    12. where empno=7844;
    13.  
    14. ---闪回事务查询，sys用户才可以查询
    15.  
    16. SQL> select undo_sql from flashback_transaction_query where xid='02000B0061030000';
    17.  
    18. UNDO_SQL
    19. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    20.  
    21.  
    22.  
    23.  
    24.  
    25.  
    26.  
    27.  
    28.  
    29.  
    30.  
    31.  
    32.  
    33.  
    34.  
    35.  
    36.  
    37. 16 rows selected.
    38.  
    39. SQL>

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

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

    SQL> set transaction isolation level serializable;

    Transaction set.

    SQL>

    画面1：

    1. [oracle@oracle-db-19c ~]$ sqlplus / as sysdba
    2.  
    3. SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 24 09:51:46 2022
    4. Version 19.3.0.0.0
    5.  
    6. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    7.  
    8.  
    9. Connected to:
    10. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    11. Version 19.3.0.0.0
    12.  
    13. SQL> conn scott/tiger@PDB1;
    14. Connected.
    15. SQL> show con_name;
    16.  
    17. CON_NAME
    18. ------------------------------
    19. PDB1
    20. SQL> set transaction isolation level serializable;
    21.  
    22. Transaction set.
    23.  
    24. SQL> select distinct sal from e01;
    25.  
    26.        SAL
    27. ----------
    28.          1
    29.  
    30. SQL> select distinct sal from e01;
    31.  
    32.        SAL
    33. ----------
    34.          1
    35.  
    36. SQL>

    画面2：

    1. [oracle@oracle-db-19c ~]$ sqlplus scott/tiger@PDB1
    2.  
    3. SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 24 10:01:22 2022
    4. Version 19.3.0.0.0
    5.  
    6. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    7.  
    8. Last Successful login time: Thu Nov 24 2022 09:52:09 +08:00
    9.  
    10. Connected to:
    11. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    12. Version 19.3.0.0.0
    13.  
    14. SQL> show con_name;
    15.  
    16. CON_NAME
    17. ------------------------------
    18. PDB1
    19. SQL> 
    20. SQL> select distinct sal from e01;
    21.  
    22.        SAL
    23. ----------
    24.          1
    25.  
    26. SQL> update e01 set sal=2;
    27.  
    28. 57344 rows updated.
    29.  
    30. SQL> select distinct sal from e01;
    31.  
    32.        SAL
    33. ----------
    34.          2
    35.  
    36. SQL> commit;
    37.  
    38. Commit complete.
    39.  
    40. SQL> set transaction isolation level serializable;
    41.  
    42. Transaction set.
    43.  
    44. SQL> select distinct sal from e01;
    45.  
    46.        SAL
    47. ----------
    48.          2
    49.  
    50. 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>

    1. [oracle@oracle-db-19c ~]$ sqlplus / as sysdba
    2.  
    3. SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 24 09:57:26 2022
    4. Version 19.3.0.0.0
    5.  
    6. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    7.  
    8.  
    9. Connected to:
    10. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    11. Version 19.3.0.0.0
    12.  
    13. SQL> 
    14. SQL> 
    15. SQL> set pagesize 200
    16. SQL> set linesize 200
    17. SQL> 
    18. SQL> select tablespace_name,retention from dba_tablespaces where contents='UNDO';
    19.  
    20. TABLESPACE_NAME                RETENTION
    21. ------------------------------ -----------
    22. UNDOTBS1                       NOGUARANTEE
    23. UNDOTBS2                       NOGUARANTEE
    24.  
    25. SQL> alter tablespace UNDOTBS2 RETENTION GUARANTEE;
    26.  
    27. Tablespace altered.
    28.  
    29. SQL> show parameter undo
    30.  
    31. NAME                                 TYPE        VALUE
    32. ------------------------------------ ----------- ------------------------------
    33. temp_undo_enabled                    boolean     FALSE
    34. undo_management                      string      AUTO
    35. undo_retention                       integer     900
    36. undo_tablespace                      string      UNDOTBS1
    37. SQL> select tablespace_name,retention from dba_tablespaces where contents='UNDO';
    38.  
    39. TABLESPACE_NAME                RETENTION
    40. ------------------------------ -----------
    41. UNDOTBS1                       NOGUARANTEE
    42. UNDOTBS2                       GUARANTEE
    43.  
    44. SQL>

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

    1. SQL> select name from v$tablespace;
    2.  
    3. NAME
    4. ------------------------------
    5. SYSAUX
    6. SYSTEM
    7. UNDOTBS1
    8. USERS
    9. TEMP01
    10. SYSTEM
    11. SYSAUX
    12. UNDOTBS1
    13. TEMP
    14. SYSTEM
    15. SYSAUX
    16. UNDOTBS1
    17. TEMP
    18. USERS
    19. TEMP02
    20. UNDOTBS2
    21. UNDOTBS2
    22.  
    23. 17 rows selected.
    24.  
    25. SQL> alter session set container=PDB1;
    26.  
    27. Session altered.
    28.  
    29. SQL> drop tablespace TEMP02 including contents and datafiles;
    30.  
    31. Tablespace dropped.
    32.  
    33. SQL> 
    34. SQL> alter system set undo_tablespace=undotbs2;
    35.  
    36. System altered.
    37.  
    38. SQL> 
    39. SQL> 
    40. SQL> alter system set undo_tablespace=undotbs1;
    41.  
    42. System altered.
    43.  
    44. SQL> drop tablespace UNDOTBS2 including contents and datafiles;
    45.  
    46. Tablespace dropped.
    47.  
    48. SQL> alter session set container=CDB$ROOT;
    49.  
    50. Session altered.
    51.  
    52. SQL> drop tablespace UNDOTBS2 including contents and datafiles;
    53.  
    54. Tablespace dropped.
    55.  
    56. SQL> select name from v$tablespace;
    57.  
    58. NAME
    59. ------------------------------
    60. SYSAUX
    61. SYSTEM
    62. UNDOTBS1
    63. USERS
    64. TEMP01
    65. SYSTEM
    66. SYSAUX
    67. UNDOTBS1
    68. TEMP
    69. SYSTEM
    70. SYSAUX
    71. UNDOTBS1
    72. TEMP
    73. USERS
    74.  
    75. 14 rows selected.
    76.  
    77. SQL>
  • 相关阅读:
    计算机毕业设计Java教评系统(源码+系统+mysql数据库+lw文档)
    虹科技术|如何阻止供应链攻击？
    广州华锐互动：VR虚拟现实物理学习平台，开启数字化教学新格局
    全民拼购模式：无论成败皆有所得
    windows DOM 命令手册
    nginx生成https 证书-基于docker-compose
    Office Word 中的宏
    官媒代运营：如何将内容营销做到深入人心
    这些大佬告诉你，在先进计算与AI领域该往哪个方向冲！
    文心智能体平台介绍和应用：制作你的智能体(运维小帮手)
  • 原文地址：https://blog.csdn.net/u011868279/article/details/127995144