扩展或缩小undo表空间，缩小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
****************************************************************************************************************************
一. 查出数据库当前的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#;二．用查询结果替代下面的部分，然后执行脚本Create UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 100M REUSE AUTOEXTEND ON; Alter SYSTEM SET UNDO_TABLESPACE=UNDOTBS2; Drop TABLESPACE INCLUDING CONTENTS AND DATAFILES; Select * FROM DBA_TABLESPACES; Create UNDO TABLESPACE DATAFILE SIZE REUSE AUTOEXTEND ON; Alter SYSTEM SET UNDO_TABLESPACE=UNDOTBS1; Drop TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES; Select * FROM DBA_TABLESPACES;
处理方法有两种，
一是添加undo 表空间的数据文件，
二是切换undo tablespace. 这种情况下多用在undo 表空间已经非常大的情况。
1 增加数据文件
sql> alter tablespace undo add datafile 'D:\undo02.dbf' size 100m reuse;
表空间已更改。
2 切换undo 表空间
1、建立新的表空间undotbs2
sql> create undo tablespace undotbs2 datafile 'D:\undo03.dbf' size 100m reuse;
表空间已创建。
2、切换到新建的undo表空间上来,操作如下
sql> alter system set undo_tablespace=undotbs2 scope=both;
系统已更改。
3、将原来的undo表空间,置为脱机:
sql> alter tablespace undo offline;
表空间已更改。
4、删除原来的undo表空间:
sql> drop tablespace undo including contents and datafiles cascade constraints
表空间已删除。
如果只是drop tablespace undo ，则只会在删除控制文件里的记录，并不会物理删除文件。
drop undo表空间的时候必须是在未使用的情况下才能进行。
如果undo表空间正在使用（例如事务失败，但是还没有恢复成功），那么drop表空间命令将失败。在drop表空间的时候可以使用including contents。