利用脚本自动生成增加数据文件的sql语句，让你轻松高效应对 Oracle 表空间扩容！ --转载：https://www.modb.pro/db/1862145748071104512?utm_source=index_ori

脚本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. 减少人为错误
   通过自动化脚本执行，减少了手动输入时可能产生的错误，提升了数据库运维的准确性和可靠性。