The rules below can be adapted to suit your script.
Identify the tablespace (unextended) usage >=90% and the remaining free space <=100000M;
Identify the \ or/before part of the data file name as the path to the data file (Windows and Linux are distinguished);
The suffix name is recognized, but the added use uses ‘.dbf’, the specification;
Identify the maximum size of the datafile corresponding to the datafile number and add a new file with the name “datafile path “+” tablespace name “+” existing datafile name “+ 1″+”.dbf”; According to the requirements of the company, the newly added data file is incremented from 1G to 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 the PREFIX, -- for the last one. ' 'and after character AS a SUFFIX SUFFIX SUBSTR (FILE_NAME, INSTR (FILE_NAME,'. ', -1)) AS SUFFIX, -- identify the first 3 digits of the last '.'(if not, start with the last digit), if not, specify the default '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 If there is no suffix identifying '.', add 00. DBF as the filename when querying. Otherwise the same output 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 'FROM DBA_TABLESPACES WHERE BIGFILE = 'NO' '||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 update