做了个移动表空间的测试
我想把数据库orcl里面的某个表空间，移到另一个数据库epma
先建一些表空间，以及用户
create tablespace tts1 datafile 'D:\ORADATA\ORCL\TTS1.DBF' SIZE 10M
autoextend on next 10M maxsize unlimited
segment space management auto
extent management local ;
create user tts identified by tts default tablespace tts1
temporary tablespace temp quota unlimited on tts1 ;
grant connect ,resource to tts ;
建一些测试的表
create table testtts(id number,name varchar2(10)) ;
alter table testtts add constraint pk_testtts primary key(id) using index tablespace tts1 ;
select * from user_indexes ;
首先把要转移的表设置为只读
alter tablespace tts1 read only ;
select * from dba_directories ;
用如下语句导出元数据信息
set ORACLE_SID=orcl
expdp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES=tts1 dumpfile=tts1.dump
下面是cmd里面的详细过程：
C:\Documents and Settings\lisx>expdp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES=y tablespaces=tts
1 dumpfile=tts1.dump
Export: Release 10.2.0.1.0 - Production on 星期四, 12 8月, 2010 11:15:28
Copyright (c) 2003, 2005, Oracle. All rights reserved.
用户名: sys as sysdba
口令:
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
UDE-00010: 已请求多个作业模式, tablespace 和 transport_tablespaces。
上面的语句报错，原来expdp转移表空间和exp是有区别的，exp的格式是
EXP transport_tablespace=y tablespaces=tts1,tts2 file=expdat.dmp
注意transport_tablespace后面没有s,不是复数形式的
而用expdp的正确的导出如下：
C:\Documents and Settings\lisx>expdp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES=tts1 dumpfile=tts
1.dump
Export: Release 10.2.0.1.0 - Production on 星期四, 12 8月, 2010 11:16:06
Copyright (c) 2003, 2005, Oracle. All rights reserved.
用户名: sys as sysdba
口令:
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYS"."SYS_EXPORT_TRANSPORTABLE_01": sys/******** AS SYSDBA directory=DATA_PUMP_DIR TRANSPORT_
TABLESPACES=tts1 dumpfile=tts1.dump
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
D:\ADMIN\ORCL\DPDUMP\TTS1.DUMP
作业 "SYS"."SYS_EXPORT_TRANSPORTABLE_01" 已于 11:16:41 成功完成
下面导入到新的数据库epma
set ORACLE_SID=epma
impdp directory=DATA_PUMP_DIR dumpfile=TTS1.DUMP TRANSPORT_DATAFILES='D:\oradata\epma\TTS1.DBF'
C:\Documents and Settings\lisx>impdp directory=DATA_PUMP_DIR dumpfile=TTS1.DUMP TRANSPORT_DATAFILES=
'D:\oradata\epma\TTS1.DBF'
Import: Release 10.2.0.1.0 - Production on 星期四, 12 8月, 2010 11:29:16
Copyright (c) 2003, 2005, Oracle. All rights reserved.
用户名: sys as sysdba
口令:
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=T
TS1.DUMP TRANSPORT_DATAFILES='D:\oradata\epma\TTS1.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-29342: 数据库中不存在用户 TTS
作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 11:29:26 停止
一定要先建用户TTS，否则报错
create user tts identified by tts ;
grant connect ,resource to tts ;
C:\Documents and Settings\lisx>impdp directory=DATA_PUMP_DIR dumpfile=TTS1.DUMP TRANSPORT_DATAFILES=
'D:\oradata\epma\TTS1.DBF'
Import: Release 10.2.0.1.0 - Production on 星期四, 12 8月, 2010 11:30:51
Copyright (c) 2003, 2005, Oracle. All rights reserved.
用户名: sys as sysdba
口令:
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=T
TS1.DUMP TRANSPORT_DATAFILES='D:\oradata\epma\TTS1.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已于 11:31:10 成功完成
**/
查看发现原来表空间里面的内容果然导入到新的表空间里面了
select * from user_indexes ;
查看状态也是只读
select * from dba_tablespaces ;
select * from dba_data_files ;
alter tablespace tts1 read write ;