识别表空间(非扩展)使用率并自动生成添加数据文件脚本识别表空间(非扩展)使用率并自

【识别表空间(非扩展)使用率并自动生成添加数据文件脚本】规则
可以根据以下规则调整为适合自己使用的脚本。

识别表空间(非扩展)使用率>=90%，且剩余可用空间<=100000M;
识别数据文件名中\或/之前部分，作为数据文件路径（区分Windows及Linux）;
识别了后缀名，但是添加使用使用了'.dbf'，规范；
识别表空间对应数据文件序号的最大号，并以"数据文件路径"+"表空间名字"+"已有数据文件名字数值最大号+1"+".dbf"命名添加新文件；按公司要求，新加数据文件1g递增扩展到24g;

SET LINES 120 PAGESIZE 1000 col commend for a200; WITH SUB_FILE AS (SELECT distinct TABLESPACE_NAME AS TBS_NAME, FILE_NAME, regexp_substr(FILE_NAME,'^.*[\\|\/]') AS PREFIX, -- 获取最后1个'.'及以后字符作为后缀名SUFFIX SUBSTR(FILE_NAME, INSTR(FILE_NAME, '.', -1)) AS SUFFIX, -- 识别最后一个'.'(没有则从最后一位开始)前面3位为数字的部分，没有则指定默认值'01'，有则截取数值部分作为数据文件编号FN TO_NUMBER(NVL(REGEXP_SUBSTR(FILE_NAME, '[0-9]+', DECODE(INSTR(FILE_NAME, '.', -1), 0, LENGTH(FILE_NAME) - 3, INSTR(FILE_NAME, '.', -1)) - 3, 1), 01)) AS FN FROM (SELECT TABLESPACE_NAME, -- FILE_NAME 如果没有'.'标识的后缀，查询时添加00.dbf作为文件名，否则原样输出 DECODE(INSTR(FILE_NAME, '.', -1), 0, FILE_NAME || '00.dbf', FILE_NAME) AS FILE_NAME FROM DBA_DATA_FILES DDF WHERE TABLESPACE_NAME IN -- 过滤掉大文件表空间，它只能有一个数据文件 (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE BIGFILE = 'NO'))) SELECT 'ALTER TABLESPACE '||SFL.TBS_NAME||' ADD DATAFILE '''||SFL.PREFIX||SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1,'FM9900'))||'.dbf'' SIZE 1M AUTOEXTEND ON; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 1G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 2G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 3G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 4G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 5G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 6G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 7G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 8G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 9G; '||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 10G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 11G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 12G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 13G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 14G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 15G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 16G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 17G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 18G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 19G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 20G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 21G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 22G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 23G; ' ||chr(10)|| 'ALTER DATABASE DATAFILE ''' || SFL.PREFIX || SFL.TBS_NAME||(TO_CHAR(SFL.FN + 1, 'FM9900'))|| '.dbf'' RESIZE 24G; ' AS COMMEND FROM SUB_FILE SFL WHERE (SFL.TBS_NAME, SFL.FN) IN (SELECT SF1.TBS_NAME, MAX(SF1.FN) FROM SUB_FILE SF1 GROUP BY SF1.TBS_NAME) AND SFL.TBS_NAME IN (SELECT D.TABLESPACE_NAME FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) AND ROUND((1 - NVL(F.FREE_SPACE, 0) / D.SPACE) * 100, 2) >= 90 AND F.FREE_SPACE <= 100000);

2021.07.07 更新