目前archive表空间需扩容 ， 但服务器资源有限 ， 无法对表空间进行扩容 ， 故采取表空间迁移方式 ， 以下为测试环境模拟 。
1、查看表空间情况 SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName, round(SUM(a.bytes/1024/1024/1024),2) AS "Totle_size(G)", round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Free_space(G)", round(SUM(a.bytes/1024/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Used_space(G)", ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0))) *100/SUM(a.bytes/1024/1024/1024),2) AS "Used_percent%", round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_size(G)",ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_percent%" FROM dba_data_files a, (SELECT SUM(NVL(bytes,0)) free_space1, file_id FROM dba_free_space GROUP BY file_id ) b WHERE a.file_id = b.file_id(+) GROUP BY a.TABLESPACE_NAMEORDER BY "Used_percent%" desc

本文插图

2、查看大表 select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024/1024 GB from dba_segments where tablespace_name="ARCHIVE" order by bytes desc

3、将大表迁移到目标表空间 注意move一个表到另外一个表空间时,索引不会跟着一起move ， 而且会失效 。 （LOB类型例外）
alter table ARCHIVE.S_SHIP_UNIT_LINE_DMP move tablespace LOB3alter table ARCHIVE.ORDER_RELEASE_LINE_DMP move tablespace LOB3
4、move索引 注意：index_type包括两种类型"NORMAL"为普通表 ， "LOB"为blob或者clob字段生成的索引 ， 在这里要排除掉
--生成重新move索引语句SELECT "alter index " || index_name || " rebuild tablespace LOB3" FROM dba_indexes WHERE index_type = "NORMAL" AND table_owner = "ARCHIVE" AND table_name="S_SHIP_UNIT_LINE_DMP" AND dropped = "NO"
5、检查表状态及索引信息 select owner,table_name,tablespace_name,status from dba_tables where table_name="S_SHIP_UNIT_LINE_DMP"select owner,index_name,index_type,table_name,status from dba_indexes where table_name="S_SHIP_UNIT_LINE_DMP"

本文插图


本文插图

6、查看表空间情况 可以发现表空间大小已更改 。

SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName, round(SUM(a.bytes/1024/1024/1024),2) AS "Totle_size(G)", round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Free_space(G)", round(SUM(a.bytes/1024/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Used_space(G)", ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0))) *100/SUM(a.bytes/1024/1024/1024),2) AS "Used_percent%", round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_size(G)",ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_percent%" FROM dba_data_files a, (SELECT SUM(NVL(bytes,0)) free_space1, file_id FROM dba_free_space GROUP BY file_id ) b WHERE a.file_id = b.file_id(+) GROUP BY a.TABLESPACE_NAMEORDER BY "Used_percent%" desc