1. 查询某个表空间最大的block_id的值:
select MAX(block_id) from dba_extents where tablespace_name = '表空间名';
2. 到命令窗口使用命令查看每个block的大小:
show parameter db_block_size
3. 查询表空间最小需占用的空间:
select block_id*db_block_size/1024/1024 from dual;
4. alter database datafile '表空间文件路径' resize [步骤3计算出来的大小];
5. 构建收缩表语句:
SELECT DISTINCT 'alter table ' || segment_name || ' SHRINK SPACE CASCADE;' FROM dba_extents WHERE tablespace_name = 'USERS' AND segment_type = 'TABLE'
6. 构建表移动表空间语句:
SELECT DISTINCT 'alter table ' || segment_name || ' MOVE TABLESPACE "TABLESPACE_NAME";' FROM dba_extents WHERE tablespace_name = 'USERS' AND segment_type = 'TABLE'