规定
能够依据以下规定调整为适宜本人应用的脚本。
辨认表空间 (非扩大) 使用率 >=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);