oracle表空间满排查以及处理步骤

oracle表空间满排查以及处理步骤

问题排查

查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME)                                                                 "表空间名",
       D.TOT_GROOTTE_MB                                                                         "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES                                                         "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",
       F.TOTAL_BYTES                                                                            "空闲空间(M)",
       F.MAX_BYTES                                                                              "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
             ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
             ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
      FROM SYS.DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME) F,
     (SELECT DD.TABLESPACE_NAME,
             ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
      FROM SYS.DBA_DATA_FILES DD
      GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

查看表空间是否具有自动扩展的能力

SELECT T.TABLESPACE_NAME,
       D.FILE_NAME,
       D.AUTOEXTENSIBLE,
       D.BYTES,
       D.MAXBYTES,
       D.STATUS
FROM DBA_TABLESPACES T,
     DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;

db文件操作

给文件赋予增长权限

alter database datafile '/opt/app/oracle/oradata/orcl/test01.dbf' autoextend on maxsize unlimited;

新增db文件

Alter tablespace USERS add datafile '/opt/app/oracle/oradata/orcl/test01.dbf' size 1024M autoextend on next 1024M Maxsize UNLIMITED;

问题结局

系统表空间满，需要扩充表空间。

解决方法一

给表空间增加数据文件，并允许自动增长。

ALTER TABLESPACE SYSTEM ADD DATAFILE '/opt/app/oracle/oradata/orcl/test01.dbf' SIZE 6000M AUTOEXTEND ON NEXT 6000M MAXSIZE 20000M;

解决方法二

允许已存在的数据文件自动增长。

alter database datafile '/opt/app/oracle/oradata/orcl/test01.dbf' autoextend on next 6000M maxsize unlimited;

解决方法三

手工改变已存在数据文件大小。

alter database datafile '/opt/app/oracle/oradata/orcl/test01.dbf' resize 6000M;

补充表空间说明

oracle可管理的最大数据块为2的22次方个，而根据单个数据块大小大小的不同，其最大容量也是不同的。对于OLTP应用，数据块的大小通常为8K，这样，算下来，单个数据文件的大小最大为(2^22)*8K=32G。

既使指定“不限制”，其最大空间也是不能超过可管理最大空间的上限的。

1、修改表空间数据文件大小为不限制的语句。

alter databasedatafile '/opt/app/oracle/oradata/orcl/test01.dbf' autoextend on maxsize unlimited;

2、创建表空间数据文件大小为不限制的语句为。

create tablespacedemo2 datafile '/opt/app/oracle/oradata/orcl/test01.dbf' size 100M autoextend on maxsizeunlimited;