缩小Oracle的系统表空间(SYSTEM、TEMP、UNDOTBS1、SYSAUX）

一、基础环境

 操作系统：Windows 或 Linux

数据库版本：Oracle Database 11.2.0.1.0  及以上版本

二、解决问题

随着使用时间的增长，Oracle 的系统表空间(SYSTEM、TEMP、UNDOTBS1、SYSAUX）会变的越来越大，会导致系统的磁盘空间不足。我们需要定期对数据库进行维护保证数据库的安全稳定运行并提高数据库的运行效率。

表空间	说明
SYSTEM	系统表空间，用于存储系统的数据字典、系统的管理信息和用户数据表等。
SYSAUX	辅助系统表空间。用于减少系统表空间的负荷，提高系统的作业效率。该表空间由Oracle系统内部自动维护，一般不用于存储用户数据。
TEMP	临时表空间。用于存储临时的短期活动的数据，例如SQL排序时产生的临时数据。数据库中的所有用户都使用temp作为默认的临时表空间。临时表空间本身是永久存在的，只是保存在临时表空间中的段是临时的。临时段在实例关闭时被释放。
UNDOTBS1	撤销表空间。用于回退未提交的事务数据。在撤销表空间中，除了回退段以外，不能建立任何其他类型的段。所以，用户不可以在撤销表空间中创建任何数据库对象
USERS	用户表空间。用于存储永久性用户对象和私有信息

三、操作步骤

 说明：以下步骤适用 Windows 或 Linux 只需要修改为不同的操作系统文件路径格式

1、缩减临时表空间（包含系统临时表空间 TEMP 和用户自建的临时表空间）的大小

 a)  以 DBA 的身份登陆SQL plus ；

sqlplus / as sysdba;

 b) 执行以下 SQL 语句：拼接缩小表空间的语句，该语句可以将临时表空间缩小到最小。

SELECT 'ALTER TABLESPACE '||TABLESPACE_NAME ||' SHRINK SPACE ;' FROM DBA_TEMP_FILES;

c)  将查询结果复制到SQL plus 里面执行即可。

d) 完成。该方法在生产环境中测试没有问题。

2、缩小系统表空间（指 SYSTEM 表空间）

a)  以 DBA 的身份登陆SQL Plus ；

sqlplus / as sysdba;

b)  如果系统开启审计功能，可以将通过以下命令将审计日志表清空。（此步骤只针对 SYSTEM 表空间，其他表空间请跳过）

TRUNCATE TABLE SYS.AUD$;

c) 查询需要缩小表空间对应的数据文件的 FILE_ID。根据FILE_NAME 确定需要缩小的数据文件的 FILE_ID

SELECT FILE_ID, FILE_NAME
  FROM DBA_DATA_FILES
 WHERE TABLESPACE_NAME = 'SYSTEM';

d) 查询该数据文件中数据所在的数据块的最大位置

 SELECT MAX(BLOCK_ID)*8/1024 FROM DBA_EXTENTS WHERE FILE_ID=1;

e) 查询结果为：721.4375 即：该数据文件中数据所在的数据块的最大位置为 721.4375MB 处。

f) 修改该数据文件的尺寸。根据需要调整数据文件的大小，但是不能低于上一步骤的查询值。数据文件的路径为步骤 b) 的FILE_NAME 值。

ALTER DATABASE DATAFILE 'D:\PROGRAMFILES\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' RESIZE 750M;

g)  完成。该方法为通用方法，即适用于其他表空间。

3、缩小UNDOTBS 表空间 

a) UNDOTBS 表空间的作用为：(1)、 Read Consistent（一致性读）(2)、Transaction Rollback（事务回滚）(3)、Transaction Recovery（事务恢复）时间越长，该表空间可能越大，但表空间只用于临时存储所以可以清理。可以通过创建新的UNDO 表空间替换旧的 UNDO 表空间。

b) 以 DBA 的身份登陆SQL Plus ；

sqlplus / as sysdba;

c) 创建一个新的 UNDO 表空间

CREATE UNDO TABLESPACE UNDOTBS2
DATAFILE 'D:\PROGRAMFILES\ORACLE\ORADATA\ORCL\UNDOTBS2.DBF'
SIZE 100M
REUSE AUTOEXTEND ON;

d) 设置新的 UNDO 表空间为系统的 UNDO 表空间

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

e）重启数据库，以下命令相当于 shutdown abort + startup

STARTUP FORCE

f) 删除旧的 UNDO 表空间及数据文件

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

g) 至此，UNDOTBS01 表空间缩减完毕。如果想要还原原来的表空间名称，重新执行该方法即可。

参考资料：

1、对于undotbs01.dbf文件太大的处理办法-阿里云开发者社区

2、oracle--缩小表空间大小 - Java Oracle SQL技术 - 博客园

3、Oracle 临时表空间过大问题，temp01.dbf过大 - 简书