作者：Digital Observer（施嘉伟） Oracle ACE Pro: Database PostgreSQL ACE Partner 11年数据库行业经验，现主要从事数据库服务工作 拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证 ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师 公众号/墨天轮：Digital Observer；CSDN/PGfans：施嘉伟；ITPUB：sjw1933
1. 概述
本文档是针对某个特定用户表空间收缩的文档，实际操作要结合生产库具体情况。主要包括以下几个流程：
2. 时间规划
| 操作类型 | 预估时间 | 实际时间 |
|---|---|---|
| 数据库信息收集 | 30min | 10min |
| 降低高水位线 | 30min | 20min |
| Resize 数据文件 | 30min | 10min |
3. 详细步骤
3.1. 收集数据库相关信息
3.1.1. 查看表空间使用情况
SQL> SELECT total.tablespace_name, Round(total.MB, 2) AS Total_MB, Round(total.MB - free.MB, 2) AS Used_MB, Round(( 1 - free.MB / total.MB ) * 100, 2) || '%' AS Used_PctFROM (SELECT tablespace_name, Sum(bytes) / 1024 / 1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free, (SELECT tablespace_name, Sum(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) totalWHERE free.tablespace_name = total.tablespace_name; TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT------------------------------ ---------- ---------- -----------------------------------------SYSAUX 5000 1359.19 27.18% SYSTEM 1024 625.13 61.05% TS_AUD 4000 1483.19 37.08% TS_BJCA 512 118.63 23.17% TS_BT 512 .06 .01% TS_CHDD 2000 864.06 43.2% TS_DATA 2167482 496135.81 22.89% TS_FCCH 512 1.94 .38% TS_FDC 50000 36266.88 72.53% TS_INDEX 390000 294789.81 75.59% TS_LOG 30000 23551.81 78.51% TS_RMAN 512 266.06 51.97% TS_SB 6000 4594 76.57% TS_SCJY 10000 5618.5 56.19% TS_WHMS 512 310.38 60.62% TS_ZLPT 2048 484.06 23.64% UNDOTBS1 60000 59999.63 100% USERS 5000 1060.06 21.2% # 与客户描述的一致，TS_DATA表空间量大，但实际使用量小。
3.1.2. 查看表空间上的用户
SQL> select owner,tablespace_name ,sum(bytes)/1024/1024/1024 G from dba_segments where tablespace_name = 'TS_DATA' group by owner,tablespace_name; OWNER TABLESPACE_NAME G------------------------------ ------------------------------ ----------TMC TS_DATA 454.085266 IBMS_SEC TS_DATA .005004883 PLATFORM TS_DATA .290893555 SZFDC TS_DATA .661071777 GTB_APP TS_DATA .20111084 FDC_ZJJYJZX TS_DATA 7.76208496 SZFDCOA TS_DATA 1.01373291 BWPLATFORM TS_DATA 16.6900024 SZFDC_CA TS_DATA 2.73272705 FDC_YJZX TS_DATA 1.35357666 FLOOR TS_DATA 108.474243 SHENBAO TS_DATA .002502441 IBMS TS_DATA 1.8303833 FCCH TS_DATA .26361084 PUCHA TS_DATA .19140625 FDCYS TS_DATA 7.56640625 RIS_ACCESS TS_DATA 1.08044434 17 rows selected.
3.1.3. 查看表空间TOP 10大表
SQL> select * from ( 2 select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024/1024 "G" 3 from dba_segments where segment_type = 'TABLE' and tablespace_name = 'TS_DATA' order by bytes desc) 4 where rownum <= 10; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME G------------------- ------------------------------------ ------------------ ------------------- ----------TMC FDC_INFO_QRY_LOG_2020 TABLE TS_DATA 144.490234 TMC TMC_LOG TABLE TS_DATA 86.6728516 TMC BIN$tvmjhUBAC37gVAALXeBfRg==$0 TABLE TS_DATA 50.9677734 TMC BIN$tvSoznk+ICjgVAALXeBfRg==$0 TABLE TS_DATA 30.59375 TMC REMC_WS_LOG_REQUEST TABLE TS_DATA 9.87109375 FDCYS SYS_20191171126389483_TEMP TABLE TS_DATA 3.4375 BWPLATFORM SOA_SYSTEM_LOG TABLE TS_DATA 3.125 FDCYS TRADE_RECORD TABLE TS_DATA 2.90625 TMC JG_TBL_FDC_EST_REGISTE TABLE TS_DATA 2.87890625 TMC MLOG$_FDC_TMC_CONTRACT TABLE TS_DATA 1.44433594
3.1.4. 查看表空间TOP 10大索引
SQL> select * from ( 2 select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024/1024 "G" 3 from dba_segments where segment_type = 'INDEX' and tablespace_name = 'TS_DATA' order by bytes desc) 4 where rownum <= 10; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME G----------------- ---------------------------- ------------------ ------------------ ----------TMC TMC_LOG_PK INDEX TS_DATA 13.03125 BWPLATFORM SOA_SYSTEM_LOG INDEX TS_DATA .433837891 TMC INDEX_USER INDEX TS_DATA .419921875 TMC INDEX_ORGAN INDEX TS_DATA .390625 BWPLATFORM PK_PLAT_USER_LOGIN_LOG INDEX TS_DATA .267150879 TMC SEI_IX_NAME INDEX TS_DATA .216796875 TMC FTVC_IX_EXPIRE_DATE INDEX TS_DATA .1953125 FDCYS I_TR_YWBJSJ INDEX TS_DATA .1953125 BWPLATFORM SYS_C0066879 INDEX TS_DATA .1484375 TMC FTVC_UK_CERT_NO_FO_ID INDEX TS_DATA .1328125 10 rows selected.
3.1.5. 查看表空间管理方式
SQL> select tablespace_name, block_size,contents, extent_management, segment_space_management, allocation_type, segment_space_management from dba_tablespaces where tablespace_name='TS_DATA'; TABLESPACE_NAME BLOCK_SIZE CONTENTS EXTENT_MAN SEGMEN ALLOCATIO SEGMEN------------------------------ ---------- --------- ---------- ------ --------- ------TS_DATA 8192 PERMANENT LOCAL AUTO SYSTEM AUTO
3.1.6. 查看数据文件使用情况
SQL> col file_name for a50 SQL> select file_name, tablespace_name, bytes/1024/1024/1024 "used G", maxbytes/1024/1024/1024 "total G" from dba_data_files where tablespace_name = 'TS_DATA' order by bytes; FILE_NAME TABLESPACE_NAME used G total G-------------------------------------------------- ------------------------------ ---------- ----------/Oradata/2nd_fdc/TS_DATA74.dbf TS_DATA 5 0 /Oradata/2nd_fdc/TS_DATA76.dbf TS_DATA 5 0 /Oradata/2nd_fdc/TS_DATA75.dbf TS_DATA 5 0 /Oradata/2nd_fdc/TS_DATA38.dbf TS_DATA 19.53125 0 /Oradata/2nd_fdc/TS_DATA39.dbf TS_DATA 19.53125 0 /Oradata/2nd_fdc/TS_DATA40.dbf TS_DATA 19.53125 0 /Oradata/2nd_fdc/TS_DATA41.dbf TS_DATA 19.53125 0 /Oradata/2nd_fdc/TS_DATA42.dbf TS_DATA 19.53125 0 /Oradata/2nd_fdc/TS_DATA65.dbf TS_DATA 19.53125 0 /Oradata/2nd_fdc/TS_DATA68.dbf TS_DATA 29.2773438 29.296875 /Oradata/2nd_fdc/TS_DATA69.dbf TS_DATA 29.2773438 29.296875 /Oradata/2nd_fdc/TS_DATA03.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA04.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA05.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA06.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA07.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA08.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA09.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA10.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA15.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA16.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA17.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA20.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA21.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA22.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA23.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA24.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA25.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA26.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA28.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA29.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA30.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA31.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA32.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA33.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA34.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA35.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA36.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA43.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA44.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA45.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA46.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA47.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA48.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA49.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA50.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA51.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA52.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA53.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA54.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA55.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA56.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA57.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA58.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA59.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA60.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA61.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA62.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA63.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA64.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA66.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA67.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA02.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA37.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA01.dbf TS_DATA 29.296875 0 /Oradata/2nd_fdc/TS_DATA71.dbf TS_DATA 30 0 /Oradata/2nd_fdc/TS_DATA72.dbf TS_DATA 30 0 /Oradata/2nd_fdc/TS_DATA73.dbf TS_DATA 30 0 /Oradata/2nd_fdc/TS_DATA70.dbf TS_DATA 30 0 /Oradata/2nd_fdc/TS_DATA27.dbf TS_DATA 31.9550781 31.9999847 /Oradata/2nd_fdc/TS_DATA12.dbf TS_DATA 31.9921875 31.9999847 /Oradata/2nd_fdc/TS_DATA13.dbf TS_DATA 31.9921875 31.9999847 /Oradata/2nd_fdc/TS_DATA18.dbf TS_DATA 31.9921875 31.9999847 /Oradata/2nd_fdc/TS_DATA19.dbf TS_DATA 31.9921875 31.9999847 /Oradata/2nd_fdc/TS_DATA11.dbf TS_DATA 31.9921875 31.9999847 /Oradata/2nd_fdc/TS_DATA14.dbf TS_DATA 31.9921875 31.9999847 76 rows selected.
3.1.7. 查看表空间高水位线情况
SQL> select max(block_id)*8/1024/1024 "G size" from dba_extents where tablespace_name='TS_DATA'; G size----------31.9920731
3.2. 降低高水位线
3.2.1. 检查失效索引（建议处理高水位前和后都要检查。并及时处理失效索引）
--检查分区索引（包括分区表的分区本地索引，分区表的分区全局索引），如果存在重建，并修改并行度 select 'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name ||' parallel 10;' from dba_ind_partitions a where a.status='UNUSABLE'; select 'alter index '||a.index_owner||'.'||a.index_name||' parallel 1;' from dba_ind_partitions a where a.status='UNUSABLE’; --检查普通索引(包括普通表的索引，分区表的普通全局索引)，如果存在重建，并修改并行度 select 'alter index '||a.owner||'.'||a.index_name||' rebuild parallel 10;' from dba_indexes a where a.status='UNUSABLE'; select 'alter index '||a.owner||'.'||a.index_name||' parallel 1;' from dba_indexes a where a.status='UNUSABLE’;
3.2.2. 查询指定用户中的高水位
# 比较表的行数和表的大小关系。如果行数为0，而表的当前占用大小减去初始化时的大小（INITIAL_EXTENT）后依然很大，那么说明该表有高水位。=====为了保证结果准确，建议先对表进行统计信息收集。# 查看TMC用户高水位SQL> SELECT D.OWNER, 2 ROUND(D.NUM_ROWS / D.BLOCKS, 2), 3 D.NUM_ROWS, 4 D.BLOCKS, 5 D.TABLE_NAME, 6 ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_size 7 FROM DBA_TABLES D 8 WHERE D.BLOCKS > 10 9 AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5 10 AND d.OWNER = 'TMC' ; OWNER ROUND(D.NUM_ROWS/D.BLOCKS,2) NUM_ROWS BLOCKS TABLE_NAME T_SIZE-------- ---------------------------- ---------- ---------- ------------------------------ ----------TMC 2.6 2597 1000 EX_ZQ_MIDDLE_ACCESS 8 TMC 2.31 30 13 EX_ZW_SRV_CONFIG 0 TMC 1.1 66 60 FDC_ROLES_CATALOG -1 TMC .35 21 60 FDC_ORGANTYPE_SUBSYSTEM -1 TMC .1 6 58 FDC_USER_DEV_UPLOAD 0 TMC 3.05 1149260 377166 JG_TBL_FDC_EST_REGISTE 2947 TMC 0 0 1522 JG_TBL_JSYDGH 12 TMC 0 0 2410 JG_TBL_LAND_CONTRACT 19 TMC 4.83 713497 147670 FDC_YS_CONTRACT_APPENDIX 1154 TMC 1.94 3646 1882 SIREA_REPORT_3 15 TMC 2.56 625 244 DZZZ_FDC_YS_PREPROJECT 2 TMC .22 9703 44390 FDC_ORGAN_REPORT_DEV_USER 347 TMC 0 0 13 SYS_20185311110401376_TEMP 0 TMC 1.57 1902326 1210229 REMC_WS_LOG_REQUEST 9455# 查看表上边的索引SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'JG_TBL_FDC_EST_REGISTE'; INDEX_NAME STATUS------------------------------ --------EST_REGISTE_PARCEL_NO VALID EST_REGISTE_PROJ_NO VALID SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'FDC_YS_CONTRACT_APPENDIX'; INDEX_NAME STATUS------------------------------ --------SYS_IL0000060570C00006$$ VALID SYS_IL0000060570C00020$$ VALID FYCA_IX_FYC_ID VALID FYCA_IX_YS_FYB_ID VALID SYS_IL0000060570C00026$$ VALID FYCA_PK VALID 6 rows selected. SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'REMC_WS_LOG_REQUEST'; INDEX_NAME STATUS------------------------------ --------SYS_IL0000142741C00026$$ VALID SYS_IL0000142741C00027$$ VALID SYS_IL0000142741C00028$$ VALID# 查看FDCYS用户高水位线SELECT D.OWNER, ROUND(D.NUM_ROWS / D.BLOCKS, 2), D.NUM_ROWS, D.BLOCKS, D.TABLE_NAME, ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_size FROM DBA_TABLES D WHERE D.BLOCKS > 10 AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5 AND d.OWNER = 'FDCYS' ; OWNER ROUND(D.NUM_ROWS/D.BLOCKS,2) NUM_ROWS BLOCKS TABLE_NAME T_SIZE------------------------------ ---------------------------- ---------- ---------- ------------------------------ ----------FDCYS 0 0 449636 SYS_20191171126389483_TEMP 3513# 查看表上的索引SQL> select index_name,status from dba_indexes where owner = 'FDCYS' and table_name = 'SYS_20191171126389483_TEMP'; INDEX_NAME STATUS------------------------------ --------SYS_C0060562 VALID
3.2.1. 收缩指定表的高水位线
# TMC用户收缩高水位线alter table TMC.JG_TBL_FDC_EST_REGISTE enable row movement;alter table TMC.JG_TBL_FDC_EST_REGISTE shrink space;alter index TMC.EST_REGISTE_PARCEL_NO rebuild;alter index TMC.EST_REGISTE_PROJ_NO rebuild;alter table TMC.JG_TBL_FDC_EST_REGISTE disable row movement;alter table TMC.FDC_YS_CONTRACT_APPENDIX enable row movement;alter table TMC.FDC_YS_CONTRACT_APPENDIX shrink space;alter index TMC.FYCA_IX_FYC_ID rebuild parallel 4 nologging;alter index TMC.FYCA_IX_YS_FYB_ID rebuild parallel 4 nologging;alter index TMC.FYCA_PK rebuild parallel 4 nologging;alter index TMC.FYCA_IX_FYC_ID parallel 1 ;alter index TMC.FYCA_IX_YS_FYB_ID parallel 1 ;alter index TMC.FYCA_PK parallel 1 ;alter index TMC.FDC_YS_CONTRACT_APPENDIX disable row movement;alter table TMC.REMC_WS_LOG_REQUEST enable row movement;alter table TMC. REMC_WS_LOG_REQUEST shrink space;alter table TMC.REMC_WS_LOG_REQUEST disable row movement;# FDCYS用户收缩高水位线alter table FDCYS.SYS_20191171126389483_TEMP enable row movement;alter table FDCYS.SYS_20191171126389483_TEMP shrink space;alter index SYS_C0060562 rebuild;alter table FDCYS.SYS_20191171126389483_TEMP disable row movement;
3.2.2. 重新收集表的统计信息
exec dbms_stats.gather_table_stats('TMC','JG_TBL_FDC_EST_REGISTE',cascade=>true);
exec dbms_stats.gather_table_stats('TMC','FDC_YS_CONTRACT_APPENDIX',cascade=>true);
exec dbms_stats.gather_table_stats('TMC','REMC_WS_LOG_REQUEST',cascade=>true);
exec dbms_stats.gather_table_stats('FDCYS','SYS_20191171126389483_TEMP',cascade=>true);
如果是大表，建议使用如下语句进行统计信息收集
说明：需要替换用户名、表名、及分区名（如果是分区表）
如果要单独分析表的某个分区，将 --granularity和-- partname=>'p200902', 这句注释去掉，并替换分区名 begin
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'NETFORCE',
tabname=>'TBL_PROCESS_DRAFT',
--granularity => 'PARTITION',
--partname=>'POPERATIONPROCESS0102',
estimate_percent=>1,
method_opt=>'FOR ALL COLUMNS SIZE 1',
no_invalidate=>false,
cascade=>true,
degree => 10);
end ;
/
3.3. Resize 数据文件
# 查看最大可resize的数据文件大小col name for a50 col resizecmd for a90select a.file#,a.name, a.bytes / 1024 / 1024 CurrentMB,ceil(HWM * a.block_size / 1024 / 1024) Resizeto, (a.bytes - HWM * a.block_size) / 1024 / 1024 releaseMB,'alter database datafile ''' || a.name || ''' resize ' ||ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCmdfrom v$datafile a, (select file_id, max(block_id + blocks - 1) HWMfrom dba_extentswhere tablespace_name = 'TS_DATA'group by file_id) bwhere a.file# = b.file_id(+)and (a.bytes - HWM * a.block_size) > 0order by 5; FILE# NAME CURRENTMB RESIZETO RELEASEMB RESIZECMD----- -------------------------------- ---------- ---------- ---------- ------------------------------------------------------------------------- 7 /Oradata/2nd_fdc/TS_DATA02.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA02.dbf' resize 30000M; 9 /Oradata/2nd_fdc/TS_DATA04.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA04.dbf' resize 30000M; 6 /Oradata/2nd_fdc/TS_DATA01.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA01.dbf' resize 30000M; 41 /Oradata/2nd_fdc/TS_DATA29.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA29.dbf' resize 30000M; 12 /Oradata/2nd_fdc/TS_DATA07.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA07.dbf' resize 30000M; 61 /Oradata/2nd_fdc/TS_DATA45.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA45.dbf' resize 30000M; 26 /Oradata/2nd_fdc/TS_DATA17.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA17.dbf' resize 30000M; 31 /Oradata/2nd_fdc/TS_DATA22.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA22.dbf' resize 30000M; 10 /Oradata/2nd_fdc/TS_DATA05.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA05.dbf' resize 30000M; 19 /Oradata/2nd_fdc/TS_DATA12.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA12.dbf' resize 32760M; 14 /Oradata/2nd_fdc/TS_DATA09.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA09.dbf' resize 30000M; 13 /Oradata/2nd_fdc/TS_DATA08.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA08.dbf' resize 30000M; 11 /Oradata/2nd_fdc/TS_DATA06.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA06.dbf' resize 30000M; 83 /Oradata/2nd_fdc/TS_DATA65.dbf 20000 20000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA65.dbf' resize 20000M; 20 /Oradata/2nd_fdc/TS_DATA13.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA13.dbf' resize 32760M; 18 /Oradata/2nd_fdc/TS_DATA11.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA11.dbf' resize 32760M; 24 /Oradata/2nd_fdc/TS_DATA15.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA15.dbf' resize 30000M; 15 /Oradata/2nd_fdc/TS_DATA10.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA10.dbf' resize 30000M; 49 /Oradata/2nd_fdc/TS_DATA36.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA36.dbf' resize 30000M; 39 /Oradata/2nd_fdc/TS_DATA27.dbf 32722 32722 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA27.dbf' resize 32722M; 25 /Oradata/2nd_fdc/TS_DATA16.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA16.dbf' resize 30000M; 8 /Oradata/2nd_fdc/TS_DATA03.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA03.dbf' resize 30000M; 30 /Oradata/2nd_fdc/TS_DATA21.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA21.dbf' resize 30000M; 45 /Oradata/2nd_fdc/TS_DATA32.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA32.dbf' resize 30000M; 29 /Oradata/2nd_fdc/TS_DATA20.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA20.dbf' resize 30000M; 27 /Oradata/2nd_fdc/TS_DATA18.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA18.dbf' resize 32760M; 28 /Oradata/2nd_fdc/TS_DATA19.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA19.dbf' resize 32760M; 35 /Oradata/2nd_fdc/TS_DATA23.dbf 30000 30000 .125 alter database datafile '/Oradata/2nd_fdc/TS_DATA23.dbf' resize 30000M; 76 /Oradata/2nd_fdc/TS_DATA60.dbf 30000 30000 .125 alter database datafile '/Oradata/2nd_fdc/TS_DATA60.dbf' resize 30000M; 74 /Oradata/2nd_fdc/TS_DATA58.dbf 30000 30000 .125 alter database datafile '/Oradata/2nd_fdc/TS_DATA58.dbf' resize 30000M; 21 /Oradata/2nd_fdc/TS_DATA14.dbf 32760 32760 .125 alter database datafile '/Oradata/2nd_fdc/TS_DATA14.dbf' resize 32760M; 50 /Oradata/2nd_fdc/TS_DATA37.dbf 30000 30000 .1875 alter database datafile '/Oradata/2nd_fdc/TS_DATA37.dbf' resize 30000M; 40 /Oradata/2nd_fdc/TS_DATA28.dbf 30000 30000 .1875 alter database datafile '/Oradata/2nd_fdc/TS_DATA28.dbf' resize 30000M; 44 /Oradata/2nd_fdc/TS_DATA31.dbf 30000 30000 .3125 alter database datafile '/Oradata/2nd_fdc/TS_DATA31.dbf' resize 30000M; 58 /Oradata/2nd_fdc/TS_DATA43.dbf 30000 30000 .3125 alter database datafile '/Oradata/2nd_fdc/TS_DATA43.dbf' resize 30000M; 43 /Oradata/2nd_fdc/TS_DATA30.dbf 30000 30000 .3125 alter database datafile '/Oradata/2nd_fdc/TS_DATA30.dbf' resize 30000M; 67 /Oradata/2nd_fdc/TS_DATA51.dbf 30000 30000 .3125 alter database datafile '/Oradata/2nd_fdc/TS_DATA51.dbf' resize 30000M; 82 /Oradata/2nd_fdc/TS_DATA64.dbf 30000 30000 .5625 alter database datafile '/Oradata/2nd_fdc/TS_DATA64.dbf' resize 30000M; 56 /Oradata/2nd_fdc/TS_DATA42.dbf 20000 20000 .625 alter database datafile '/Oradata/2nd_fdc/TS_DATA42.dbf' resize 20000M; 85 /Oradata/2nd_fdc/TS_DATA67.dbf 30000 29973 27.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA67.dbf' resize 29973M; 84 /Oradata/2nd_fdc/TS_DATA66.dbf 30000 29645 355.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA66.dbf' resize 29645M; 102 /Oradata/2nd_fdc/TS_DATA74.dbf 5120 3580 1540.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA74.dbf' resize 3580M; 90 /Oradata/2nd_fdc/TS_DATA70.dbf 30720 29121 1599.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA70.dbf' resize 29121M; 103 /Oradata/2nd_fdc/TS_DATA75.dbf 5120 3422 1698.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA75.dbf' resize 3422M; 91 /Oradata/2nd_fdc/TS_DATA71.dbf 30720 28993 1727.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA71.dbf' resize 28993M; 104 /Oradata/2nd_fdc/TS_DATA76.dbf 5120 3295 1825.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA76.dbf' resize 3295M; 88 /Oradata/2nd_fdc/TS_DATA69.dbf 29980 22035 7945.9375 alter database datafile '/Oradata/2nd_fdc/TS_DATA69.dbf' resize 22035M; 86 /Oradata/2nd_fdc/TS_DATA68.dbf 29980 21958 8022.9375 alter database datafile '/Oradata/2nd_fdc/TS_DATA68.dbf' resize 21958M; 92 /Oradata/2nd_fdc/TS_DATA72.dbf 30720 21246 9474.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA72.dbf' resize 21246M; 93 /Oradata/2nd_fdc/TS_DATA73.dbf 30720 20626 10094.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA73.dbf' resize 20626M;
4. 结论
本次表空间高水位线收缩动作共释放50G左右空间。 通过合理地收缩表空间高水位，不仅可以有效释放磁盘空间，还能优化数据库的存储结构，提升整体性能表现。在实际运维过程中，掌握Shrinking、Move、Rebuild等手段的适用场景与操作细节，能够帮助我们更高效地解决空间浪费问题。希望本文提供的实践操作和经验总结，能为广大DBA在日常维护中提供借鉴与参考。
数据库管理虽无银弹，但精益求精的每一步，终将让系统更加稳定、可靠、高效。
