undo表空间

SQL> select * from v$version;  

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

 

--查看当前的undo表空间 

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

 

 

SQL> col tablespace_name  for a20;
SQL> select tablespace_name ,contents from dba_tablespaces;

TABLESPACE_NAME      CONTENTS
--------------------             ---------
SYSTEM                         PERMANENT
SYSAUX                         PERMANENT
TEMP                             TEMPORARY
USERS                           PERMANENT
UNDOTBS2                    UNDO
EXAMPLE                       PERMANENT

 

--创建undo表空间 

SQL>create  UNDO  tablespace  myundotbs1 datfile '/u01/oradata/undotbs1.db size 100m;

 

--删除undo表空间

SQL>drop tablespace undotbs2；

 

-修改undo_retention

SQL>alter system set undo_retention =900 scope=both;

-- 切换undo

SQL>alter system set  undo_tablespace=undotbs2;

 

--查看当前的事物 

SQL> select addr ,used_ublk from v$transaction;

 

--查看undo的使用情况，默认记录每10分钟采集一次

SQL> select begin_time ,end_time ,undotsn ,undoblks from v$undostat;