–1、查询目标表空间数据文件(示例main表空间)尾部的表
select distinct
o.owner,
e.USED,
e.extent_id,
case o.object_type when ‘INDEX’ THEN (select i.table_name
from DBA_INDEXES i
where i.INDEX_NAME=o.object_name limit 1)
when ‘TABLE’ THEN o.object_name else null end
as table_name

from v$extents e

join V$SEGMENT_INFOS s

on e.seg_id=s.seg_id
join dba_objects o
on o.object_id=s.obj_id
left join dba_indexes i
on o.object_name=i.index_name
where e.ts_id=(select t.id from v$tablespace t where name=‘MAIN’)
order by e.extent_id desc limit 10;

–如果存在SYSJOB，先关闭定时任务
–执行前需要事先将备份定时作业的ddl
SP_INIT_JOB_SYS(0);
执行完成后重新查询如图：

–2、查询表大小和条数
–可查询表大小和条数，大致预估迁移时间
SELECT OWNER,TABLE_NAME,TABLE_USED_PAGES(OWNER,TABLE_NAME)*PAGE()/1024.0/1024.0 SIZE_MB,
SF_GET_TABLE_COUNT(A.OWNER, A.TABLE_NAME) TAB_COUNT
FROM DBA_TABLES A
WHERE A.OWNER IN (‘xx’) AND TABLE_NAME=‘表名’;

–3、新建普通表空间
create tablespace TBS_TEMP_YD datafile ‘TBS_TEMP_YD01.DBF’ size 128 CACHE = NORMAL;

–4、移动表到新建普通表空间
–先查询相关表有没有业务，建议相关表无业务时进行操作
select * from v$sessions where SQL_TEXT like ‘%PACKET MOVE%’
–调整表所属空间到新建的表空间
alter table SYSDBA.PACKET MOVE TABLESPACE TBS_TEMP_YD;
–重新查询，如图

–5、查已经移动到表空间TBS_TEMP_YD的表和索引
SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME=‘TBS_TEMP_YD’;
SELECT * FROM DBA_INDEXES WHERE TABLESPACE_NAME=‘TBS_TEMP_YD’;

–6、根据需要临时设置UNDO_RETENTION参数
本次参数默认值已经是90就不做调整
SP_SET_PARA_DOUBLE_VALUE (1,‘UNDO_RETENTION’,90);

–7、使用表空间收缩命令进行空间回收
main表空间对象已经全部清空，调整main表空间
alter tablespace “MAIN” resize datafile ‘MAIN.DBF’ to 2048;
执行成功，如图