让OracleDB自动诊断 自动扩容表空间

**导读**

> 作者：杨漆

> 16年关系型数据库管理，从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM；运维路上不平坦，跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿，希望帮到大家少走弯路、少熬夜。

Oracle运维中常出现Tablespace空间使用满，导致挂库事件。

通常出现这类事件时，需要DBA紧急处理。

如果表空间特别多，Server数量上千台，DBA就需要多次查询、手工执行枯燥的扩容任务。

如果空间不够情况正好出现在半夜、周末时，DBA怎一个苦字了得！

怎样将DBA释放出来，达到智能化运维，让DB自动诊断，自动解决表空间扩容需求？

用这套自动扩容脚本就好（我已多年不写代码了，下午写的这套Coding比较Low,仅抛砖引玉,大家可以在次基础上改写以更好地适应自己的DB运维环境）

### Oracle表空间使用情况监控

## 改成 GB显示

set linesize 400

set pagesize 200

set feed off

-- create table monitor_tablespace_rate (tbs_name varchar2(50),TOTAL_GB number,USED_GB number, FREE_GB number,RATE number, MAXEXTEND_GB number);

truncate table monitor_tablespace_rate;

insert into monitor_tablespace_rate select * from

(SELECT D.TABLESPACE_NAME TBS_NAME,

D.TOT_GROOTTE_MB TOTAL_GB,

D.TOT_GROOTTE_MB - F.TOTAL_BYTES USED_GB,

F.TOTAL_BYTES FREE_GB,

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.MAXEXTEND_MB * 100,

2),

'990.99') RATE,

D.MAXEXTEND_MB MAXEXTEND_GB

FROM (SELECT TABLESPACE_NAME,

Round(Sum(NVL(BYTES,0)) / (1024 * 1024 * 1024), 2) TOTAL_BYTES,

Round(Max(NVL(BYTES,0)) / (1024 * 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 * 1024), 2) TOT_GROOTTE_MB,

Round(Sum(DECODE(DD.MAXBYTES,0,DD.BYTES,DD.MAXBYTES)) / (1024 * 1024 * 1024), 2) MAXEXTEND_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

AND D.TABLESPACE_NAME NOT LIKE '%UNDO%'

ORDER BY 5 desc);

-- 创建存储过程，可用剩余空间小于15%时自动扩容tablespace

create or replace procedure proc_monitor_tbs_rate

as

file_num int;

file_name varchar2(200);

new_file_name varchar2(200);

str varchar2(300) ;

begin

for i in (select TBS_NAME,RATE from monitor_tablespace_rate) loop

if i.rate>85 then

select file_name,max(file_id) into file_name,file_num from dba_data_files where tablespace_name=i.TBS_NAME group by file_name;

file_num:=file_num+1;

new_file_name :=substr(file_name,1,instr(file_name,'/',-1))||i.TBS_NAME||file_num;

str :='alter tablespace '||i.TBS_NAME||' add datafile '||''''||new_file_name||''''|| ' size 1G autoextend on';

execute immediate str;

DBMS_OUTPUT.PUT_LINE(i.TBS_NAME||' add '||new_file_name);

end if;

end loop;

end;

/

-- 创建定时任务，每小时执行一次 (存储过程里的;号不能省略)

variable jobno number;

begin

dbms_job.submit(:jobno,'proc_monitor_tbs_rate;', sysdate, 'sysdate+1/24');

commit;

end;

/

-- 查看定时任务情况

select job, next_date, next_sec, failures, broken from user_jobs;

JOB NEXT_DATE NEXT_SEC FAILURES B

---------- ------------------ -------------------------------- ---------- -

3 22-JUL-21 17:52:54 0 N

-- 任务生成成功，Job号为3

-- 停止定时任务

begin

dbms_job.broken(3, true, sysdate);

commit;

end;

/

-- 启动定时任务

begin

dbms_job.run(3);

commit;

end;

/

分享给大家，希望大家可以少加班、少熬夜。