1、正常扩容缩容
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN (‘TS_BP1’,‘TS_BP2’,‘TS_BP3’)
ORDER BY TABLESPACE_NAME;

ALTER TABLESPACE TS_BP3 RESIZE DATAFILE ‘TS_BP3.DBF’ TO 256;
ALTER TABLESPACE TS_BP3 RESIZE DATAFILE ‘TS_BP3.DBF’ TO 128;

2、高水位下缩容
2.1 创建测试表
CREATE TABLE T_HWM_TEST (
ID INT,
PAD VARCHAR(2000)
) STORAGE(ON TS_BP3);
2.2 插入大量数据
BEGIN
FOR i IN 1…100000 LOOP
INSERT INTO T_HWM_TEST VALUES (i, RPAD(‘x’, 2000, ‘x’));
IF MOD(i, 1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
2.3 确认文件已自动扩展
SELECT FILE_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = ‘TS_BP3’;

2.4 查看高水位对象名
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, EXTENT_ID
FROM DBA_EXTENTS
WHERE TABLESPACE_NAME = ‘TS_BP3’
ORDER BY EXTENT_ID DESC;

2.5 计算最小可缩容大小
SELECT
c.name AS tablespace_name,
a.path AS file_name,
CEIL(NVL(b.hwm, 1) * (SF_GET_PAGE_SIZE()/1024.0/1024.0)) AS “min_mb_by_HWM”
FROM V$DATAFILE a LEFT JOIN ( SELECT ts_id, file_id, MAX(1.0 * V_EXT.EXTENT_ID * SF_GET_EXTENT_SIZE() + V_EXT.USED) AS hwm FROM V$EXTENTS AS V_EXT
WHERE state NOT IN (‘FREE’) AND seg_id > 0
GROUP BY ts_id, file_id
) b ON (a.id = b.file_id AND a.group_id = b.ts_id)
JOIN VTABLESPACE c ON a.GROUP_ID = c.ID WHERE a.path LIKE '%TS_BP3.DBF';  2.6 高水位阻碍缩容（预期失败） ALTER TABLESPACE TS_BP3 RESIZE DATAFILE 'TS_BP3.DBF' TO 128;  2.7 清理表 -- 删除表，释放所有扩展区，生产环境请勿操作 DROP TABLE T_HWM_TEST; 2.8 再次计算最小可缩容大小 SELECT c.name AS tablespace_name, a.path AS file_name, CEIL(NVL(b.hwm, 1) * (SF_GET_PAGE_SIZE()/1024.0/1024.0)) AS "min_mb_by_HWM" FROM VDATAFILE a
LEFT JOIN (
SELECT ts_id, file_id,
MAX(1.0 * V_EXT.EXTENT_ID * SF_GET_EXTENT_SIZE() + V_EXT.USED) AS hwm
FROM V$EXTENTS AS V_EXT WHERE state NOT IN ('FREE') AND seg_id > 0 GROUP BY ts_id, file_id ) b ON (a.id = b.file_id AND a.group_id = b.ts_id) JOIN V$TABLESPACE c ON a.GROUP_ID = c.ID
WHERE a.path LIKE ‘%TS_BP3.DBF’;

2.9 缩容成功
ALTER TABLESPACE TS_BP3 RESIZE DATAFILE ‘TS_BP3.DBF’ TO 128;
SELECT FILE_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = ‘TS_BP3’;