dmp文件查看表空间_分享一份Oracle大表迁移不同表空间教程

概述

目前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 LOB3;alter 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;

---

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！