Identify tablespace (non extended) usage and automatically generate scripts to add data files

Time:2021-9-5

rule
You can adjust the script to suit your use according to the following rules.

Identify that the utilization rate of table space (non extended) is > = 90%, and the remaining available space is < = 100000m;
Identify the part before \ or / in the data file name as the data file path (distinguish between windows and Linux);
The suffix is recognized, but the ‘. DBF’ specification is added;
Identify the maximum number of data file serial number corresponding to the table space, and add a new file with the name of “data file path” + “table space name” + “maximum number of existing data file name value + 1” + “. DBF”; According to the requirements of the company, 1g of newly added data file is incrementally expanded 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 PREFIX,
         --Get the last '.' and later characters as the suffix suffix
         SUBSTR(FILE_NAME, INSTR(FILE_NAME, '.', -1)) AS SUFFIX,
         --Identify the part with the first three digits of the last '.' (if not, start from the last digit). If not, specify the default value of '01'. If yes, intercept the numerical part as the data file number 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 identified by '.', add 00.dbf as the file name during query, otherwise output as is
                 DECODE(INSTR(FILE_NAME, '.', -1), 0, FILE_NAME || '00.dbf', FILE_NAME) AS FILE_NAME
            FROM DBA_DATA_FILES DDF
           WHERE TABLESPACE_NAME IN
          --Filter out the large file table space, which can only have one data file
                 (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 update

Recommended Today

“Self test” stay up late to summarize 50 Vue knowledge points, all of which will make you God!!!

preface Hello everyone, I’m Lin Sanxin. A lot of things have happened these days (I won’t say what’s specific). These things have scared me to treasure my collection these yearsVue knowledge pointsI took out my notes and tried my best to recall them. Finally, I realized these 50Knowledge points(let’s not be too vulgar. It’s not […]