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

一、基础环境

      操作系统：Windows 或 Linux

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

二、解决问题

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

三、操作步骤

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

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

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

sqlplus / as sysdba;

      b) 执行以下 SQL 语句：其中 100M 如果觉的太小可以自己重新设置。临时表空间一般为自动扩展。

SELECT ‘ALTER TABLESPACE ‘||TABLESPACE_NAME ||‘ SHRINK SPACE KEEP 100M;‘ FROM DBA_TEMP_FILES;

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

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

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

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

sqlplus / as sysdba;

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

1.  
   SELECT FILE_ID, FILE_NAME
2.  
   FROM DBA_DATA_FILES
3.  
   WHERE TABLESPACE_NAME = ‘SYSTEM‘;

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

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

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

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

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

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

      3、缩小UNDOTBS 表空间 

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

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

sqlplus / as sysdba;

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

1.  
   CREATE UNDO TABLESPACE UNDOTBS2
2.  
   DATAFILE ‘D:\PROGRAMFILES\ORACLE\ORADATA\ORCL\UNDOTBS2.DBF‘
3.  
   SIZE 100M
4.  
   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、https://yq.aliyun.com/articles/518452

2、https://www.cnblogs.com/tyler2000/archive/2011/01/21/1940904.html

3、https://www.jianshu.com/p/29651bace851