Oracle表空间不存在问题

一、问题引出

相信大家对如上报错应该不陌生（ORA-00959：表空间XXX不存在）。

 

二、问题分析

问题原因：涉及到源库(导出库)中的表、索引、LOB字段的表空间在目的库(导入库)中不存在的问题。

解决思路：所以该问题正确解决方式应该分三步：

1. 表的表空间迁移。
2. 索引的表空间迁移。
3. LOB字段的表空间迁移。

 

三、解决方法

3.1、表的表空间迁移

首先，我们需要知道该用户名下有哪些表空间，总不能等到我都导出库了，等导入报错后再确认吧？，这样太费事了，可以根据下条sql语句查出：

-- 查看用户的表被存储在哪些表空间中(需要修改表空间的表即在这些表空间中)
SELECT * FROM dba_users where username = upper('&inputUserName');   

然后，根据需要修改表所属的表空间：

SELECT 'alter table '||TABLE_NAME||' move tablespace dstTableSpace;' 
FROM USER_TABLES WHERE status = 'VALID' AND  TABLESPACE_NAME = upper('&srcTablespace');

Tips：我们还可以查看下在用户不同表空间下各有多少张表：

SELECT count(*) FROM dba_segments where tablespace_name = '&表空间1' and owner = '&用户名' and segment_type = 'TABLE'
UNION
SELECT count(*) FROM dba_segments where tablespace_name = '&表空间2' and owner = '&用户名' and segment_type = 'TABLE';

 

3.2、索引的表空间迁移

由3.1步知道有哪些表空间后，可以用如下sql语句批量进行索引的表空间迁移。

SELECT 'alter index '|| INDEX_NAME ||' rebuild tablespace index_DstTablespace;' FROM user_indexes where table_owner=upper('&username');

Tips：同3.1，我们也可以知道在用户不同表空间下各有多少索引：

SELECT count(*) FROM dba_segments where tablespace_name = '&表空间1' and owner = '&用户名' and segment_type = 'INDEX'
UNION
SELECT count(*) FROM dba_segments where tablespace_name = '&表空间2' and owner = '&用户名' and segment_type = 'INDEX';

 

3.3、LOB字段的表空间迁移

1、查看有哪些blob字段

SELECT a.table_name, a.column_name, a.segment_name, b.tablespace_name, b.bytes  -- b.btypes为lob字段总大小
  FROM user_lobs a, user_segments b
 where a.segment_name = b.segment_name;

2、再逐个或批量修改CLOB、BLOB字段所属表空间

-- 注意是LOB(col_lob1, col_lob2...)
ALTER TABLE TEST2 MOVE TABLESPACE table_DstTablespace LOB(col_lob1,col_lob2) STORE AS(TABLESPACE lob_DstTablespace);

 

最后

有可能还会遇到如下问题：ORA-14063:唯一/主约束条件关键字中存在无用索引：

 解决方法：rebuild无效索引。

-- 查看有哪些索引是无效的
SELECT tablespace_name,
       index_name,
       table_name,
       table_owner,
       index_type,
       status
  FROM user_indexes
 where status <> 'VALID';

-- 批量重构无效索引
select 'alter index ' || index_name || ' rebuild;'
  from user_indexes
 where tablespace_name = '&tablespaceName'
   and status <> 'VALID' --(过滤无效索引后重建)