***********************************************声明*********************************************************************** 

原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。

深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/39935157

****************************************************************************************************************************

实验项目：将undo表空间扩大，然后缩小

SQL> select name,bytes/1024/1024 M from v$datafile;

--查询undo表空间，当前为230m

NAME                                                       M

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

/u01/app/oracle/oradata/xcky/system01.dbf                760

/u01/app/oracle/oradata/xcky/sysaux01.dbf                550

/u01/app/oracle/oradata/xcky/undotbs01.dbf               230

/u01/app/oracle/oradata/xcky/users01.dbf             1518.75

/u01/app/oracle/oradata/xcky/example01.dbf               330

（1）、扩展undo表空间

思路：创建一个更大的undo表空间，让后将原undo空间替换

SQL> create undo tablespace undotbs02 datafile ‘/u01/app/oracle/oradata/xcky/undotbs02.dbf‘ size 500m reuse autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs02;

System altered.

SQL> select name,bytes/1024/1024 M from v$datafile;

--查询会发现，新扩展的undo表空间

NAME                                                       M

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

/u01/app/oracle/oradata/xcky/system01.dbf                760

/u01/app/oracle/oradata/xcky/sysaux01.dbf                550

/u01/app/oracle/oradata/xcky/undotbs01.dbf               230

/u01/app/oracle/oradata/xcky/users01.dbf             1518.75

/u01/app/oracle/oradata/xcky/example01.dbf               330

/u01/app/oracle/oradata/xcky/undotbs02.dbf               500

SQL> select tablespace_name,sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name;

--查找出之前undo表空间的名字

TABLESPACE_NAME                       MB

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

BULLETIN_INFO                        499

INDEX_EIS                             19

SYSAUX                                33

UNDOTBS1                         204.375

TBS_XZXT_PHOTO                        19

TBS_XZXT_SYSTEM                       19

VIEW_LOG                              99

UNDOTBS02                         497.75

......

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

--删除undo表空间

Tablespace dropped.

SQL> select tablespace_name,sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name;

--再次查看undo表空间已经为新扩展的undo表空间了

TABLESPACE_NAME                       MB

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

BULLETIN_INFO                        499

INDEX_EIS                             19

SYSAUX                                33

VIEW_LOG                              99

UNDOTBS02                         497.75

（2）、缩小undo表空间

思路：有了之前扩展undo表空间的方法，接下来缩小表空间便会变得简单了。原理一样，创建一个小的undo表空间，然后把大的undo表空间删除即可。

SQL> create undo tablespace UNDOTBS1 datafile ‘/u01/app/oracle/oradata/xcky/undotbs01.dbf‘ size 250m reuse autoextend on;

--创建一个小的undo表空间

Tablespace created.

SQL> alter system set UNDO_TABLESPACE=UNDOTBS1;

--改变undo表空间为UNDOTBS1

System altered.

SQL> select tablespace_name,sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name;

--再次查询，新建的undo表空间已经显示

TABLESPACE_NAME                       MB

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

BULLETIN_INFO                        499

INDEX_EIS                             19

SYSAUX                                33

UNDOTBS1                          247.75

UNDOTBS02                         497.75

SQL> drop tablespace UNDOTBS02 including contents and datafiles;

--删除大的undo表空间

Tablespace dropped.

SQL> select tablespace_name,sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name;

--查看表空间情况，已经完成了缩小undo表空间

TABLESPACE_NAME                       MB

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

BULLETIN_INFO                        499

INDEX_EIS                             19

SYSAUX                                33

UNDOTBS1                          247.75

结合以上实验过程，编写脚本，可以减轻日后的工作量，有了上面的基础，看懂下面的语句应该是so easy了吧，O(∩_∩)O哈哈~

指令如下：

脚本实现：

[root@xckydb soft]# vi query_undo.sql

--编写undo表空间查询脚本

SELECT a.name, b.name

 FROM v$tablespace a, v$datafile b

 WHERE a.name = (SELECT VALUE

                  FROM v$parameter

                 WHERE name = ‘undo_tablespace‘)

      AND a.ts# = b.ts#;

SQL> @/soft/query_undo.sql

NAME                          NAME

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

UNDOTBS1                      /u01/app/oracle/oradata/xcky/u

                              ndotbs01.dbf

[root@xckydb soft]# vi create_undo.sql

--编写undo表空间创建脚本

#create middle undo tablespace

Create UNDO TABLESPACE UNDOTBS2 DATAFILE <‘/undotbs02.dbf‘> SIZE <100M> REUSE AUTOEXTEND ON;

Alter SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

#delete old undo tablespace

Drop TABLESPACE <UNDOTBS1> INCLUDING CONTENTS AND DATAFILES;

Select * FROM DBA_TABLESPACES;

#create new undo tablespace

Create UNDO TABLESPACE <UNDOTBS1> DATAFILE <‘/undotbs01.dbf‘> SIZE <100M> REUSE AUTOEXTEND ON;

Alter SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;

#delete middle undo tablespace

Drop TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

Select * FROM DBA_TABLESPACES;

***********************************************声明*********************************************************************** 

原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。

深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/39935157

****************************************************************************************************************************

扩展或缩小undo表空间