扩大或缩小undo表空间

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

原创作品。出自 “深蓝的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

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