Oracle如何将用户下的一个表空间的数据迁移到另一个表空间

说明

前提是我们没有dba权限，然后我们要从USERS表空间迁移到自定义表空间，以下代码命名为“DIY_TABLESPACE”，将其替换为你实际的目的表空间既可。

1. 查看当前USERS表空间下的对象

-- 查看用户下所有表所在的表空间
SELECT table_name, tablespace_name 
FROM user_tables 
WHERE tablespace_name = 'USERS';

-- 查看用户下所有索引所在的表空间  
SELECT index_name, table_name, tablespace_name
FROM user_indexes
WHERE tablespace_name = 'USERS';

-- 查看用户下所有分区表的分区所在表空间
SELECT table_name, partition_name, tablespace_name
FROM user_tab_partitions
WHERE tablespace_name = 'USERS';

-- 查看用户下所有LOB字段所在的表空间
SELECT table_name, column_name, segment_name, tablespace_name
FROM user_lobs
WHERE tablespace_name = 'USERS';

2. 迁移表到新表空间（下面命名为DIY_TABLESPACE）

-- 批量生成迁移语句（推荐）
SELECT 'ALTER TABLE ' || table_name || ' MOVE TABLESPACE DIY_TABLESPACE;'
FROM user_tables
WHERE tablespace_name = 'USERS';

-- 迁移单个表
ALTER TABLE 表名 MOVE TABLESPACE DIY_TABLESPACE;

3. 迁移索引到新表空间

-- 重建索引到新表空间
-- 批量生成重建索引语句（推荐）这里面会出现一些命名中带$的索引会报失败，先跳过，等执行完clob再看看应该就没有了
SELECT 'ALTER INDEX ' || index_name || ' REBUILD TABLESPACE DIY_TABLESPACE;'
FROM user_indexes
WHERE tablespace_name = 'USERS';

-- 单独迁移一张表
ALTER INDEX 索引名 REBUILD TABLESPACE DIY_TABLESPACE;

4. 迁移LOB字段到新表空间

-- 批量生成迁移LOB语句（推荐）
SELECT 'ALTER TABLE ' || table_name || ' MOVE LOB(' || column_name || 
       ') STORE AS (TABLESPACE DIY_TABLESPACE);'
FROM user_lobs
WHERE tablespace_name = 'USERS';

-- 迁移表的LOB字段
ALTER TABLE 表名 MOVE LOB(LOB字段名) STORE AS (TABLESPACE DIY_TABLESPACE);

5. 迁移分区表

-- 迁移所有分区（推荐）
SELECT 'ALTER TABLE ' || table_name || ' MOVE PARTITION ' || partition_name || 
       ' TABLESPACE DIY_TABLESPACE;'
FROM user_tab_partitions
WHERE tablespace_name = 'USERS';

-- 迁移特定分区
ALTER TABLE 表名 MOVE PARTITION 分区名 TABLESPACE DIY_TABLESPACE;

6.检查索引状态，将离线的索引上线

-- 生成所有失效索引的重建语句
SELECT 'ALTER INDEX ' || index_name || 
       ' REBUILD TABLESPACE DIY_TABLESPACE' || 
       ' ONLINE;' AS rebuild_sql
FROM user_indexes
WHERE status = 'UNUSABLE' ;

-- 将上一步的输出结果复制出来，作为脚本执行，以下是例子。
ALTER INDEX 索引名 REBUILD TABLESPACE 表空间名 ONLINE;

上面执行完之后再检查一下索引状态是不是VALID
select status from user_indexes;

6.验证迁移结果

-- 查询为0正常
SELECT COUNT(*) 
FROM user_segments
WHERE tablespace_name = 'USERS';