undo tablespace

--1、显示undo表空间相关参数
SQL> Show Parameters Undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1


SQL>
undo_management=auto | manual 
--2、查询undo表空间
SQL> Select a.TABLESPACE_NAME,a.CONTENTS From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME                CONTENTS
------------------------------ ---------
UNDOTBS1                       UNDO
--3、创建undo表空间
SQL> Create Undo Tablespace undotbs2 Datafile 'D:\app\Administrator\oradata\orcl\undotbs2a.dbf' Size 10m Autoextend On Next 10m Maxsize Unlimited;
Tablespace created


SQL> Select a.TABLESPACE_NAME,a.CONTENTS From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME                CONTENTS
------------------------------ ---------
UNDOTBS1                       UNDO
UNDOTBS2                       UNDO


SQL> 
--4、为undo表空间增加数据文件
SQL> Alter Tablespace undotbs2 Add Datafile 'D:\app\Administrator\oradata\orcl\undotbs2b.dbf' Size 10m Autoextend On Next 10m Maxsize Unlimited;
Tablespace altered


SQL> 
--5、查询撤销表空间和数据文件
SQL> Select b.file_name,b.tablespace_name
  2  From dba_tablespaces a,dba_data_files b Where a.TABLESPACE_NAME=b.TABLESPACE_NAME And a.contents='UNDO'
  3  /
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF                                  UNDOTBS1
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF                                  UNDOTBS2
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2B.DBF                                  UNDOTBS2
--6、为撤销表空间删除数据文件
SQL> Alter Tablespace undotbs2 Drop Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2B.DBF';
Tablespace altered


SQL> Select b.file_name,b.tablespace_name
  2  From dba_tablespaces a,dba_data_files b Where a.TABLESPACE_NAME=b.TABLESPACE_NAME And a.contents='UNDO'
  3  /
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF                                  UNDOTBS1
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF                                  UNDOTBS2
--7、改变undo表空间数据文件的大小
SQL> Alter Database Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF' Resize 20m;
Database altered


SQL> 
--8、改变undo表空间数据文件的扩展属性
SQL> Alter Database Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF' Autoextend On Next 1m Maxsize 1024m;
Database altered


SQL> 
--9、切换undo表空间
SQL> alter system set undo_tablespace=undotbs2;
System altered


SQL> show parameters undo_tablespace;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2
--10、修改undo_retention(至少保留时间)
SQL> alter system set undo_retention=1200;
System altered


SQL> show parameter undo_retention;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     1200


SQL> 修改retention garentee
SQL> Alter Tablespace undotbs2 Retention Guarantee;
Tablespace altered


SQL> Select a.TABLESPACE_NAME,a.CONTENTS,a.RETENTION From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      NOGUARANTEE
UNDOTBS2                       UNDO      GUARANTEE


SQL> Alter Tablespace undotbs2 Retention Noguarantee;
Tablespace altered


SQL> Select a.TABLESPACE_NAME,a.CONTENTS,a.RETENTION From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      NOGUARANTEE
UNDOTBS2                       UNDO      NOGUARANTEE


SQL> 
--10、删除undo表空间
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped


--11、查看undo表空间状态
SQL> Select to_char(a.begin_time,'yyyymmdd hh24:mi:ss') As begin_time,to_char(a.end_time,'yyyymmdd hh24:mi:ss') As end_time,a.UNDOBLKS,a.TXNCOUNT From v$undostat a;
BEGIN_TIME        END_TIME            UNDOBLKS   TXNCOUNT
----------------- ----------------- ---------- ----------
20150831 00:38:10 20150831 00:39:49        256        483
20150831 00:28:10 20150831 00:38:10         37        265
20150831 00:18:10 20150831 00:28:10          9        152
20150831 00:08:10 20150831 00:18:10          8         84
20150830 23:58:10 20150831 00:08:10         80        334
20150830 23:48:10 20150830 23:58:10          6         76
20150830 23:38:10 20150830 23:48:10          9        130
20150830 23:28:10 20150830 23:38:10          8         93
20150830 23:18:10 20150830 23:28:10          9         76
20150830 23:08:10 20150830 23:18:10          7        204
20150830 22:58:10 20150830 23:08:10        101        186
20150830 22:48:10 20150830 22:58:10         12        113
20150830 22:38:10 20150830 22:48:10          6         80
20150830 22:28:10 20150830 22:38:10         14        192
20150830 22:18:10 20150830 22:28:10          9         80
20150830 22:08:10 20150830 22:18:10         14        365
20150830 21:58:10 20150830 22:08:10         73        160
20150830 21:48:10 20150830 21:58:10         13        283
20150830 21:38:10 20150830 21:48:10         13        117
19 rows selected


SQL>