Oracle 表空间扩容

今天接到业务工程师反馈的一个 Oracle 数据库问题，对方反馈业务停摆了，客户端报错信息如下：

通过错误号 ORA-01654 可以大致判断出故障原因是表空间满了。通过命令行工具 oerr 也可查看错误号对应的错误信息：

$ oerr ORA 01654
01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// an index segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.



后续处理步骤为查看 oracle 阈值告警信息，确认需要扩容的表空间，然后对其扩容。

Oracle 有自己的阈值管理流程，DBA_THRESHOLDS 数据字典定义了告警范围。比如表空间超85%会产生告警：

select t.metrics_name, t.warning_value
from DBA_THRESHOLDS t
where t.metrics_name = 'Tablespace Space Usage'
and t.warning_operator = 'GE'

告警信息可以通过 dba_outstanding_alerts 数据字典查看：

select OBJECT_NAME, reason, suggested_action, metric_value
from dba_outstanding_alerts;

确认产生告警信息的源头是 NNC_DATA01 表空间需要扩容。扩容前需要查询 oracle 是否启用 OMF，如果没有启用，为表空间添加文件时需要提供完整文件路径。

处理步骤为：

SQL> show parameter create


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string


SQL> col file_name for a50
SQL> select file_name, file_id, autoextensible
from dba_data_files t
where t.TABLESPACE_NAME = 'NNC_DATA01'


FILE_NAME FILE_ID AUT
-------------------------------------------------- ---------- ---
G:APPADMINISTRATORORADATAORCLNNC_DATA01.DBF 5 YES
G:APPADMINISTRATORORADATAORCLNNC_DATA02.DBF 7 NO
G:APPADMINISTRATORORADATAORCLNNC_DATA03.DBF 9 YES
G:APPADMINISTRATORORADATAORCLNNC_DATA04.DBF 10 YES
G:APPADMINISTRATORORADATAORCLNNC_DATA05.DBF 11 YES
G:APPADMINISTRATORORADATAORCLNNC_DATA06.DBF 12 YES
G:APPADMINISTRATORORADATAORCLNNC_DATA07.DBF 13 YES
G:APPADMINISTRATORORADATAORCLNNC_DATA08.DBF 14 YES


SQL> alter database datafile 7 autoextend on maxsize unlimited;


SQL> ALTER TABLESPACE NNC_DATA01 ADD DATAFILE 'G:APPADMINISTRATORORADATAORCLNNC_DATA09.DBF' SIZE 100M;


SQL> alter database datafile 15 autoextend on maxsize unlimited;

表空间扩容后，查询表空间使用率，这里需要注意计算表空间使用率时要考虑数据文件的自动增长。

查询表空间使用率的语句为：

select a.TABLESPACE_NAME,
round(TOTAL_MB/1024, 2) as Total_GB,
round((a.Free_MB + b.Free_MB) 1024, 2) as Free_GB,
ROUND((1 - (a.Free_Mb + b.Free_Mb) / a.TOTAL_MB) * 100, 2) || '%' as rate
from (
select TABLESPACE_NAME,
ROUND(sum(case
when autoextensible = 'NO' then
BYTES
when autoextensible = 'YES' then
MAXBYTES
end) / 1024 / 1024,
2) TOTAL_MB,
ROUND(sum(case
when MAXBYTES - BYTES >= 0 then
MAXBYTES - BYTES
when MAXBYTES - BYTES < 0 then
0
end) / 1024 / 1024,
2) Free_MB
from dba_data_files
group by TABLESPACE_NAME) a
inner join (SELECT dfs.TABLESPACE_NAME,
SUM(dfs.bytes / 1024 / 1024) FREE_MB
FROM dba_free_space dfs
GROUP BY dfs.TABLESPACE_NAME) b
ON A.TABLESPACE_name = b.tablespace_name
order by ROUND((1 - (a.Free_Mb + b.Free_Mb) / a.TOTAL_MB) * 100, 2) desc;



执行效果为：

全文完。

如果转发本文，文末务必注明：“转自微信公众号：生有可恋”。