针对CDB模式,PDB无sys用户,故需要使用system进行procedure创建,针对数据库字典,需要由sys授权select。https://www.cndba.cn/hbhe0316/article/103891https://www.cndba.cn/hbhe0316/article/103891https://www.cndba.cn/hbhe0316/article/103891https://www.cndba.cn/hbhe0316/article/103891
GRANT SELECT ANY DICTIONARY TO system;
GRANT ALTER TABLESPACE TO system;
GRANT CREATE TABLESPACE TO system;
CREATE OR REPLACE procedure auto_add_datafile is
ALL_file_name Varchar(500);
file_name Varchar(500);
tablespace_all varchar(500);Vs_Sql Varchar2(500);
cursor c_tablespace is
SELECT total.tablespace_name, Round(total.MB, 2) AS Total_MB,Round(total.MB - free.MB, 2) AS Used_MB, Round(( 1 -
free.MB / total.MB ) * 100, 2) AS Used_Pct
FROM (SELECT tablespace_name, Sum(bytes)/1024/1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free,
(SELECT tablespace_name,Sum(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name
and free.tablespace_name <> 'SYSTEM' AND free.tablespace_name <> 'SYSAUX' AND free.tablespace_name <> 'USERS' AND
free.tablespace_name NOT LIKE 'UNDOTBS%';
Begin
for tablespace_all in c_tablespace loop
If tablespace_all.USED_PCT >=60 Then
ALL_file_name := '/oradata/TESTDB/' || tablespace_all.tablespace_name;
ALL_file_name := ALL_file_name||'_'||to_char(sysdate,'yyyymmddhh24miss')||'.dbf';
dbms_output.put_line(ALL_file_name);
Vs_Sql := 'alter tablespace '||tablespace_all.tablespace_name||' add datafile '''||ALL_file_name||'''
size 30g autoextend on';
dbms_output.put_line(Vs_Sql);
Execute Immediate Vs_Sql;
End If;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
End auto_add_datafile;
variable jobid number;
exec dbms_job.submit(:jobid,'auto_add_datafile;',sysdate, 'sysdate+5/1440');
exec dbms_job.run(:jobid);
关于interval参数的详解:
每分钟执行 Interval => TRUNC(sysdate,’mi’) + 1/ (24*60) 或 Interval => sysdate+1/1440
每天定时执行 例如:每天的凌晨1点执行 Interval => TRUNC(sysdate) + 1 +1/ (24)
每周定时执行 例如:每周一凌晨1点执行 Interval => TRUNC(next_day(sysdate,’星期一’))+1/24
每月定时执行 例如:每月1日凌晨1点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
每季度定时执行 例如每季度的第一天凌晨1点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),’Q’) + 1/24
每半年定时执行 例如:每年7月1日和1月1日凌晨1点 Interval => ADD_MONTHS(trunc(sysdate,’yyyy’),6)+1/24
每年定时执行 例如:每年1月1日凌晨1点执行 Interval =>ADD_MONTHS(trunc(sysdate,’yyyy’),12)+1/24
针对PDB监控
CREATE OR REPLACE procedure SYSTEM.auto_add_datafile is
ALL_file_name Varchar(500);
file_name Varchar(500);
tablespace_all varchar(500);Vs_Sql Varchar2(500);
cursor c_tablespace is
SELECT total.tablespace_name, Round(total.MB, 2) AS Total_MB,Round(total.MB - free.MB, 2) AS Used_MB, Round(( 1 -
free.MB / total.MB ) * 100, 2) AS Used_Pct
FROM (SELECT tablespace_name, Sum(bytes)/1024/1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free,
(SELECT tablespace_name,Sum(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name
and free.tablespace_name <> 'SYSTEM' AND free.tablespace_name <> 'SYSAUX' AND free.tablespace_name <> 'USERS' AND
free.tablespace_name NOT LIKE 'UNDOTBS%';
Begin
for tablespace_all in c_tablespace loop
If tablespace_all.USED_PCT >=60 Then
ALL_file_name := '/oradata/TESTDB/' || tablespace_all.tablespace_name;
ALL_file_name := ALL_file_name||'_'||to_char(sysdate,'yyyymmddhh24miss')||'.dbf';
dbms_output.put_line(ALL_file_name);
Vs_Sql := 'alter tablespace '||tablespace_all.tablespace_name||' add datafile '''||ALL_file_name||'''
size 30g autoextend on';
dbms_output.put_line(Vs_Sql);
Execute Immediate Vs_Sql;
End If;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
End auto_add_datafile;
variable jobid number;
exec dbms_job.submit(:jobid,'auto_add_datafile;',sysdate, 'sysdate+5/1440');
exec dbms_job.run(:jobid);
版权声明:本文为博主原创文章,未经博主允许不得转载。
oracle