作为 DBA，我们时常需要处理表空间扩容的任务。手动添加数据文件不仅繁琐，而且容易出错。为了提高效率和减少人为失误，今天带来了两段可以自动化生成扩容语句的脚本，它适用于不同操作系统环境，助力 DBA 高效管理数据库的表空间。

脚本1：对指定表空间，生成扩容语句

脚本内容如下：

set linesize 150 pagesize 900
SELECT 'alter tablespace ' || TABLESPACE_NAME || ' add datafile ''' ||
       substr(file_name, 1, regexp_instr(file_name, '[[:digit:]]+\.') - 1) ||
       TO_CHAR(substr(file_name,
                      regexp_instr(file_name, '[[:digit:]]+\.'),
                      instr(file_name, '.') -
                      regexp_instr(file_name, '[[:digit:]]+\.')) + B.RN) ||
       '.dbf'' size ' || bytes/1024/1024 || 'm autoextend on;'
  FROM DBA_DATA_FILES,
       (select rownum rn
          from dba_objects
         WHERE ROWNUM <= &number_of_datafile) B
 WHERE FILE_ID = (SELECT DISTINCT LAST_VALUE(FILE_ID) 
 OVER(ORDER BY to_number(substr(file_name, regexp_instr(file_name, '[[:digit:]]+\.'), instr(file_name, '.') - regexp_instr(file_name, '[[:digit:]]+\.'))) ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                    FROM DBA_DATA_FILES
                   WHERE tablespace_name = '&tablespace_name');

脚本首先要求输入两个参数：

• number_of_datafile：指定需要扩容的数据文件数。
• tablespace_name：指定需要扩容的表空间名称。

根据这些输入，脚本会自动生成扩容 SQL 语句。它通过查询 DBA_DATA_FILES 视图，获取当前表空间的数据文件路径，生成新的数据文件名并附加扩容命令。

此 SQL 语句会根据文件路径生成新的数据文件名，确保扩容后的数据文件顺序递增。扩容 SQL 会自动设置新文件的大小为当前文件大小（当然，你也可以更改一下脚本，给它一个固定值，如100m），并启用自动扩展（autoextend on）。

运行后，结果如下：

输入 number_of_datafile 的值:  2
输入 tablespace_name 的值:  USERS

'ALTERTABLESPACE'||TABLESPACE_NAME||'ADDDATAFILE'''||SUBSTR(FILE_NAME,1,REGEXP_INSTR(FILE_NAME,'[[:DIGIT:]]+\.')-1)||TO_CHAR(SUBSTR(FILE_NAME,REGEXP_I
------------------------------------------------------------------------------------------------------------------------------------------------------
alter tablespace USERS add datafile 'D:\ORADATA\ORCL\USERS2.dbf' size 4292m autoextend on;
alter tablespace USERS add datafile 'D:\ORADATA\ORCL\USERS3.dbf' size 4292m autoextend on;

确认生成的扩容sql没有问题后，就可以执行了，对需要的表空间进行扩容！

脚本2：根据表空间使用率，生成扩容语句

接下来的脚本，是更加智能的功能：根据表空间的使用率自动扩容，脚本内容如下：

SET SERVEROUTPUT ON;
DECLARE
  file_num      INT;
  file_name     VARCHAR2(200);
  new_file_name VARCHAR2(200);
  str           VARCHAR2(300);
  path_separator VARCHAR2(1);
BEGIN
  -- 动态判断操作系统路径分隔符
  IF INSTR(UPPER(SYS_CONTEXT('USERENV', 'OS_USER')), '\') > 0 THEN
    path_separator := '\';
  ELSE
    path_separator := '/';
  END IF;

  FOR i IN (SELECT TBS_NAME, RATE
              FROM (SELECT dbf.tablespace_name "TBS_NAME",
                           ROUND(dbf.totalspace, 2) "Total(M)",
                           ROUND(dbf.used - dfs.freespace, 2) "Use(M)",
                           ROUND(NVL(dfs.freespace, 0) + dbf.totalspace - dbf.used, 2) "Free(M)",
                           ROUND(((dbf.used - NVL(dfs.freespace, 0)) / dbf.totalspace) * 100, 2) "RATE"
                      FROM (SELECT t.tablespace_name,
                                   SUM(GREATEST(t.maxbytes, t.bytes)) / 1024 / 1024 AS totalspace,
                                   SUM(t.bytes) / 1024 / 1024 AS used
                              FROM dba_data_files t
                             GROUP BY t.tablespace_name) dbf
                      LEFT JOIN (SELECT tt.tablespace_name,
                                       SUM(tt.bytes) / 1024 / 1024 freespace
                                  FROM dba_free_space tt
                                 GROUP BY tt.tablespace_name) dfs
                        ON dbf.tablespace_name = dfs.tablespace_name)
            ) LOOP
    IF i.RATE > 6 THEN
      SELECT COUNT(file_id)
        INTO file_num
        FROM dba_data_files
       WHERE tablespace_name = i.TBS_NAME;

      file_num := file_num + 1;

      SELECT file_name
        INTO file_name
        FROM dba_data_files
       WHERE tablespace_name = i.TBS_NAME
         AND ROWNUM = 1;

      new_file_name := SUBSTR(file_name, 1, INSTR(file_name, path_separator, -1)) ||
                       i.TBS_NAME || '0' || file_num || '.dbf';

      str := 'ALTER TABLESPACE ' || i.TBS_NAME ||
             ' ADD DATAFILE ' || '''' || new_file_name || '''' ||
             ' SIZE 100M AUTOEXTEND ON;';

      DBMS_OUTPUT.PUT_LINE(str);
      --execute immediate str; -- 若需要自动执行扩容SQL，取消注释此行
    END IF;
  END LOOP;
END;
/

这段脚本的主要目的是：

1. 根据表空间的使用率，当其使用超过 80% 时，自动生成扩容命令，避免空间不足带来的性能问题。
2. 动态判断操作系统环境，生成符合系统要求的路径分隔符。

通过查询 DBA_DATA_FILES 和 DBA_FREE_SPACE 视图，脚本根据 RATE 值判断表空间的使用情况，如果超过 80%，会生成一个新的数据文件并附加到指定表空间。路径分隔符（/ 或 \）会根据操作系统自动判断，以确保在不同平台下都能正常工作。该过程不仅自动化了扩容操作，还帮助 DBA 定期检查和维护数据库的健康状态。

脚本输出结果如下：

Windows 环境

ALTER TABLESPACE USERS ADD DATAFILE 'D:\ORADATA\USERS02.dbf' SIZE 100M AUTOEXTEND ON;

Linux 环境

ALTER TABLESPACE USERS ADD DATAFILE '/u01/oradata/USERS02.dbf' SIZE 100M AUTOEXTEND ON;

如果要让脚本帮你自动执行扩容，取消 --execute immediate str; 这行前面的注释即可。不过还是建议你先生成语句，确认一遍没问题了再执行更稳妥。

为何要使用这两段脚本？

1. 提高效率
   自动化生成扩容 SQL，避免了手动计算和命名数据文件的繁琐过程。DBA可以将更多时间投入到其他更具挑战性的任务中。
2. 防止空间不足
   定期检查表空间使用率，及时扩容，避免因空间不足导致的性能下降或服务中断。
3. 兼容不同操作系统
   生成适合不同环境的文件路径格式，减少了平台切换带来的不便。
4. 减少人为错误
   通过自动化脚本执行，减少了手动输入时可能产生的错误，提升了数据库运维的准确性和可靠性。

结语

通过这两段自动生成扩容表空间的 SQL 脚本，DBA 不仅能高效地管理表空间的扩容问题，还能确保数据库始终保持足够的空间以应对业务增长。无论是日常维护还是突发需求，掌握这样的自动化脚本都能大大提升工作效率。如果你还在为表空间扩容而烦恼，不妨试试这段脚本，简化你的数据库运维工作！