管理表空间（三）--UNDO表空间

 

undo tablespace存放undo数据块，可以建立多个undo 表空间，但处于active状态只有一个，active状态的undo tablespace是不能

offline 和drop。

如果未建立undo tablespace oracle 使用system undo segment。（如果没有建立undo表空间，就默认用system表空间作为undo表空间

，这是很不好的）

1、查看undo表空间

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      rtbs

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE STATUS                      CONTENTS

---------- --------------------------- ---------------------------

SYSTEM     ONLINE                      PERMANENT

RTBS       ONLINE                      UNDO

SYSAUX     ONLINE                      PERMANENT

TEMP       ONLINE                      TEMPORARY

USERS      ONLINE                      PERMANENT

TEXT       ONLINE                      PERMANENT

LX01       ONLINE                      PERMANENT

7 rows selected.

2、创建undo表空间

SQL> create undo tablespace

2  undotbs datafile '/u01/app/oracle/oradata/anny/undotbs01.dbf' size 100m

3  autoextend on next 10m maxsize 500m;

Tablespace created.

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE STATUS                      CONTENTS

---------- --------------------------- ---------------------------

SYSTEM     ONLINE                      PERMANENT

RTBS       ONLINE                      UNDO

SYSAUX     ONLINE                      PERMANENT

TEMP       ONLINE                      TEMPORARY

USERS      ONLINE                      PERMANENT

TEXT       ONLINE                      PERMANENT

LX01       ONLINE                      PERMANENT

UNDOTBS    ONLINE                      UNDO

09:03:06 SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      rtbs

09:03:25 SQL> select TABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS

------------------------------ --------- ---------

SYSTEM                         ONLINE    PERMANENT

RTBS                           ONLINE    UNDO

SYSAUX                         ONLINE    PERMANENT

TEMP                           ONLINE    TEMPORARY

USERS                          ONLINE    PERMANENT

EXAMPLE                        ONLINE    PERMANENT

INDX                           ONLINE    PERMANENT

OLTP                           ONLINE    PERMANENT

TEST                           ONLINE    PERMANENT

CUUG                           ONLINE    PERMANENT

LX01                           ONLINE    PERMANENT

LX02                           ONLINE    PERMANENT

UNDO_TBS01                     ONLINE    UNDO

13 rows selected.

09:03:28 SQL> alter system set undo_tablespace=undo_tbs01;

System altered.

3、切换undo 表空间

09:04:11 SQL> show parameter undo;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDO_TBS01