Oracle之表空间迁移

问题背景：一个数据表随着时间的累积，导致所在表空间占用很高，里面历史数据可以清除，保留近2个月数据即可

首先通过delete删除了2个月以前的数据。

按网上的教程进行空间压缩，以下sql在表所在用户执行:

-- 允许表重新分配未使用的空间
ALTER TABLE your_table_name DEALLOCATE UNUSED;
 
-- 允许行移动，以便压缩表
ALTER TABLE your_table_name ENABLE ROW MOVEMENT;
 
-- 压缩表的空间
ALTER TABLE your_table_name SHRINK SPACE;
-- 或者（这个会报错，不知道什么原因）
ALTER TABLE your_table_name SHRINK STORAGE;

执行后依然不能降低表空间大小：

SELECT a.tablespace_name "表空间名",
       (total - free) "表占用空间大小",
       ROUND((total - free) / total * 100, 2) || '%' "已使用空间百分比"
  FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free
          FROM dba_free_space
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
 ORDER BY (total - free) DESC;

（1）创建新的表空间（sys用户下）

CREATE TABLESPACE new_tablespace DATAFILE 'path_to_datafile.dbf' SIZE 4096M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

new_tablespace：是新的表空间名称

path_to_datafile.dbf：表空间文件存储路径（绝对路径）

4096M：表空间初始空间大小

AUTOEXTEND ON NEXT 500M：表示空间不够时自动扩充，扩充空间位500M

MAXSIZE UNLIMITED：表示最大空间大小不设限制

（2）将表迁移到新的表空间（表所在用户）

alter table TABLE_NAME move tablespace new_tablespace;

TABLE_NAME：迁移的表名称

new_tablespace：迁移的新表空间

如果有索引的话，还需重建索引：

alter index index_name rebuild tablespace new_tablespace;

index_name：索引名

如果表中有lob字段，上面sql并不能把lob字段一起迁移到新表，因为clob字段会另外单独存储。应该使用：

ALTER TABLE my_table MOVE TABLESPACE new_tablespace INCLUDE LOB;

上面这个sql没试过，因为我先执行了上一条sql语句（不带lob字段）的迁移，结果删除表空间的时候提示还有字段在表空间，才发现clob没有迁移。

SELECT 
    SEGMENT_NAME, 
    SEGMENT_TYPE,
    TABLESPACE_NAME
FROM 
    DBA_SEGMENTS
WHERE 
    OWNER = 'SCOTT' AND (SEGMENT_TYPE like 'LOB%');

可以使用这个sql,查看lob字段所在的表空间。

如果你也先执行了第一条sql迁移表空间数据，那么可以使用下面这条sql，额外把lob字段迁移到新表空间

ALTER TABLE table_name MOVE TABLESPACE new_tablespace LOB(clob_column) STORE AS (TABLESPACE new_tablespace);

table_name:是表名，

new_tablespace：新表空间

clob_column：CLOB字段名（不需要单引号括起来直接写字段名）

(3)删除旧表空间(sys用户下)

DROP TABLESPACE CLOBS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

此sql可能会比较耗时，请耐心等待。另外这只是在oracle中把表空间删除了，但物理文件还在，需要手动把物理文件删除