规定
能够依据以下规定调整为适宜本人应用的脚本。
辨认表空间(非扩大)使用率>=90%，且残余可用空间<=100000M;
辨认数据文件名中\或/之前局部，作为数据文件门路（辨别Windows及Linux）;
辨认了后缀名，然而增加应用应用了'.dbf'，标准；
辨认表空间对应数据文件序号的最大号，并以"数据文件门路"+"表空间名字"+"已有数据文件名字数值最大号+1"+".dbf"命名增加新文件；按公司要求，新加数据文件1g递增扩大到24g;
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 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);