【Oracle数据导出并导入至新实例，涉及到不同的表空间】

Oracle数据库表空间管理

用户数据与表空间的关系

在Oracle数据库中，表空间是数据存储的逻辑容器，而用户(模式)对象(如表、索引等)则存储在指定的表空间中。以下是用户数据与表空间的关系：

1. 默认表空间：创建用户时可以指定默认表空间

   CREATE USER username IDENTIFIED BY password 
   DEFAULT TABLESPACE tablespace_name;
   

2. 临时表空间：指定用户执行排序等操作使用的临时表空间

   CREATE USER username IDENTIFIED BY password 
   DEFAULT TABLESPACE data_ts
   TEMPORARY TABLESPACE temp_ts;
   

3. 表级指定：创建表时可以显式指定表空间

   CREATE TABLE table_name (...) 
   TABLESPACE specific_ts;
   

多表空间存储实现

Oracle支持将不同对象存储在不同表空间中，实现方式包括：

1. 按对象类型分离：

   • 表存储在一个表空间
   • 索引存储在另一个表空间
   • LOB数据存储在第三个表空间

2. 按功能分离：

   • 不同业务模块使用不同表空间
   • 历史数据和当前数据分离

3. 按性能需求分离：

   • 高频访问数据放在高性能存储的表空间
   • 低频访问数据放在普通存储的表空间

示例：

-- 创建表时指定不同存储
CREATE TABLE orders (
    order_id NUMBER,
    order_data CLOB,
    CONSTRAINT pk_orders PRIMARY KEY (order_id)
)
TABLESPACE data_ts
LOB (order_data) STORE AS (TABLESPACE lob_ts);

数据导出导入与表空间关系

当使用Oracle的导出工具(如expdp/数据泵)将数据导出到新数据库时：

1. 表空间需求：

   • 目标数据库不需要有完全相同的表空间名称
   • 但需要有足够空间的相应表空间来容纳导入的数据

2. 处理方式：

   • REMAP_TABLESPACE：可以在导入时重映射表空间

     impdp system/password DUMPFILE=export.dmp 
     REMAP_TABLESPACE=source_ts:target_ts
     

   • 自动创建：如果使用TRANSPORT_TABLESPACE方式且满足条件，可以传输整个表空间
   • 默认表空间：如果没有指定且无法找到原表空间，对象会尝试创建到用户的默认表空间

3. 最佳实践：

   • 导出前记录原表空间信息
   • 在目标库预先创建足够大的表空间
   • 使用REMAP_TABLESPACE参数灵活处理表空间差异

4. 特殊情况：

   • SYSTEM和SYSAUX表空间中的对象需要特殊处理
   • 临时表空间通常不需要特别处理，使用目标库的配置即可

通过合理规划表空间和使用导入工具的参数，可以有效地在不同数据库间迁移数据，即使表空间结构不完全相同。

Oracle 跨多表空间数据导出导入方案

当源数据库用户的数据对象分布在多个表空间，而目标数据库只有一个默认表空间时，可以使用以下方法处理。

方案一：使用数据泵(expdp/impdp)并重映射表空间

1. 首先在源库导出数据

expdp system/password schemas=SOURCE_USER 
directory=DATA_PUMP_DIR dumpfile=source_user.dmp logfile=expdp_source_user.log

2. 在目标库导入时重映射所有表空间到默认表空间

impdp system/password schemas=SOURCE_USER 
directory=DATA_PUMP_DIR dumpfile=source_user.dmp logfile=impdp_source_user.log
remap_tablespace=TS1:DEFAULT_TBS,TS2:DEFAULT_TBS,TS3:DEFAULT_TBS

说明：将TS1,TS2,TS3等替换为实际的源表空间名，DEFAULT_TBS替换为目标库的默认表空间名

方案二：使用传统导出导入(exp/imp)并转换表空间

1. 源库导出

exp system/password owner=SOURCE_USER file=source_user.dmp log=exp_source_user.log

2. 目标库导入

imp system/password fromuser=SOURCE_USER touser=TARGET_USER 
file=source_user.dmp log=imp_source_user.log 
commit=y ignore=y buffer=10000000
transform=OID:n,segment_attributes:n

关键参数说明：

• transform=segment_attributes:n - 忽略原始存储属性(包括表空间)
• 数据会自动导入到目标用户的默认表空间

方案三：使用SQL脚本方式迁移(最灵活)

1. 生成DDL脚本(在源库执行)

-- 生成创建对象的DDL脚本
SELECT DBMS_METADATA.GET_DDL(u.object_type, u.object_name, u.owner) 
FROM all_objects u 
WHERE u.owner='SOURCE_USER' AND u.object_type IN ('TABLE','INDEX','SEQUENCE','VIEW')
UNION ALL
-- 生成LOB列的DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', t.table_name, t.owner) 
FROM all_tables t WHERE t.owner='SOURCE_USER'
AND EXISTS (SELECT 1 FROM all_lobs l WHERE l.owner=t.owner AND l.table_name=t.table_name);

2. 编辑生成的脚本

• 移除所有TABLESPACE "XXX"子句
• 移除所有STORAGE参数

3. 在目标库执行修改后的脚本

4. 使用数据泵只导出/导入数据

-- 导出数据
expdp system/password schemas=SOURCE_USER 
directory=DATA_PUMP_DIR dumpfile=data_only.dmp content=DATA_ONLY

-- 导入数据
impdp system/password schemas=SOURCE_USER 
directory=DATA_PUMP_DIR dumpfile=data_only.dmp content=DATA_ONLY

注意事项

1. 表空间大小：确保目标默认表空间有足够空间容纳所有数据

2. 权限问题：目标用户需要有默认表空间的使用配额

3. 性能影响：所有对象集中在一个表空间可能影响I/O性能

4. 特殊对象：包含BLOB/CLOB等大对象时需要特别处理

5. 依赖对象：确保目标库已存在所有必要的依赖对象

以上方案中，方案一(数据泵)是最推荐的方式，因为它提供了最完整的对象处理能力和最佳的性能。

在不知道源表空间信息时使用默认表空间的方法

当您不知道或不关心源数据库中的表空间信息，只想将所有对象导入到目标数据库的默认表空间时，可以使用以下几种方法：

方法一：使用数据泵(impdp)的REMAP_TABLESPACE通配方式

Oracle数据泵支持使用通配符来重映射所有表空间：

impdp system/password schemas=SOURCE_USER 
directory=DATA_PUMP_DIR dumpfile=source_user.dmp 
remap_tablespace=%.%:DEFAULT_TBS

这个命令会将所有源表空间(无论名称是什么)都映射到目标数据库的DEFAULT_TBS表空间。

方法二：使用TRANSFORM参数忽略表空间属性

impdp system/password schemas=SOURCE_USER 
directory=DATA_PUMP_DIR dumpfile=source_user.dmp 
transform=segment_attributes:n

transform=segment_attributes:n参数会忽略存储属性(包括表空间、存储参数等)，所有对象将使用目标用户的默认表空间。

方法三：结合使用两种方法(推荐)

impdp system/password schemas=SOURCE_USER 
directory=DATA_PUMP_DIR dumpfile=source_user.dmp 
remap_tablespace=%.%:DEFAULT_TBS
transform=segment_attributes:n

这种组合方式确保：

1. 所有表空间都被重映射到DEFAULT_TBS
2. 所有存储属性被忽略，使用默认值

方法四：使用传统imp工具的忽略表空间方法

如果您使用的是传统imp工具：

imp system/password fromuser=SOURCE_USER touser=TARGET_USER 
file=source_user.dmp ignore=y commit=y buffer=10000000
transform=OID:n,segment_attributes:n

验证步骤

1. 首先查询目标用户的默认表空间：

   SELECT username, default_tablespace 
   FROM dba_users 
   WHERE username = 'TARGET_USER';
   

2. 导入后验证对象所在的表空间：

   SELECT segment_name, segment_type, tablespace_name 
   FROM dba_segments 
   WHERE owner = 'TARGET_USER';
   

注意事项

1. 确保目标用户的默认表空间有足够空间：

   SELECT tablespace_name, sum(bytes)/1024/1024 "Size MB"
   FROM dba_segments 
   WHERE owner = 'SOURCE_USER'
   GROUP BY tablespace_name;
   

2. 对于系统对象(如PL/SQL代码、视图等)，即使不指定表空间也会正确导入，因为它们不占用表空间存储。

3. 如果导入过程中出现表空间错误，可以先用sqlfile参数生成DDL检查：

   impdp system/password directory=DATA_PUMP_DIR 
   dumpfile=source_user.dmp sqlfile=check_ddl.sql
   

这种方法可以确保无论源数据库使用多少个表空间，所有对象都会被导入到目标数据库的默认表空间中。