exp smsd3bi/smsd3bi@sms153 tables=(T_BD_APPLY,T_BD_APPLYMSG,T_BD_CUSTOMER,T_BD_LOGIN,T_BD_LOGINMESSAGE,T_BD_STATUS,T_BD_SUB,T_BD_VISIT,T_BD_WEEKDAY,T_BD_WEEKLY,T_BD_WEEKLYLIST,T_BD_WORK,TBL_USER_POST,TBL_SALES_FACTORY,T_DAYLOG_LOG)
create user msbd identified msbd default tablespace pm_data;
grant connect,resource,imp_full_database to msbd;
revoke unlimited tablespace from pm_data;
alter user msbd quota unlimited on pm_data;
imp msbd/msbd file=smsd3bi.dmp fromuser=smsd3bi@sms153 touser=msbd tables=(T_BD_APPLY,T_BD_APPLYMSG,T_BD_CUSTOMER,T_BD_LOGIN,T_BD_LOGINMESSAGE,T_BD_STATUS,T_BD_SUB,T_BD_VISIT,T_BD_WEEKDAY,T_BD_WEEKLY,T_BD_WEEKLYLIST,T_BD_WORK,TBL_USER_POST,TBL_SALES_FACTORY,T_DAYLOG_LOG)
常见的导入案例(迁移)
1)导入指定表到相同用户
源服务器A192.168.100.100
目标服务器B192.168.100.68
实现:将A服务器中SCOTT用户的DEPT,EMP表导入到B相同用户下,前提是两个原表的默认存储表空间相同
A:
exp scott/tiger file=scott_tbls.dmp tables=(dept,emp) log=scott_exp.log
b:
imp scott/tiger file=scott_tbls.dmp tables=(dept,emp)
2)导入指定表到其它用户
sqlplus / as sysdba
grant connect,resource,imp_full_database to scott;
alter user scott quota unlimited on books; --如果该用户对多个表空间要写,就执行多条语句
alter user scott quota 0 on users; --将原来表空间限制不能写,前提是该用户下的表不存在blob字段,否则看下面的另一个案例
revoke unlimited tablespace from scott;
imp jboss/jboss@192.168.0.68 fromuser=scott touser=jboss file=scott_tbl.dmp log=scott_imp.log
3)导入用户的所有对象到另一台服务器,但表空间不一样。
注意:A服务器用户sms表空间在users,B服务器的默认表空间在smstbs;
A:
exp sms/sms file=sms.dmp log=sms.log
B:
sqlplus / as sysdba
grant connect,resource,imp_full_database to sms;
alter user sms quota unlimited on smstbs; --如果该用户对多个表空间要写,就执行多条语句
alter user sms quota 0 on users; --将原来表空间限制不能写,前提是该用户下的表不存在blob字段,否则看下面的另一个案例
revoke unlimited tablespace from sms;
imp sms/sms file=sms.dmp fromuser=sms touser=sms grants=n tablespaces=smstbs
4)导入包含LOB类型的表,且表空间与当前用户默认表空间不一样
例如:HELLO.LOBTBL表拥有一个CLOB列,存储于HELLO表空间中,现导入到JBOSS用户下,表空间为JBS
操作过程如下:
exp hello/hello file=hello.dmp log=hello.log
alter user jboss quota unlimited on jbs;
alter user jboss quota 0 on hello;
revoke unlimited tablespace from jboss;
imp jboss/jboss fromuser=hello touser=jboss file=hello.dmp log=jboss_exp.log ignore=y
当执行上面的语句的时候,会报如下的错误:
=============================================================================
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
警告: 这些对象由 HELLO 导出, 而不是当前用户
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
IMP-00017: 由于 ORACLE 错误 1536, 以下语句失败:
"CREATE TABLE "HELLO" ("NID" NUMBER(*,0), "NAME" VARCHAR2(20), "MESSAGE" CLO"
"B) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FRE"
"ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "HELLO" LOGGING "
"NOCOMPRESS LOB ("MESSAGE") STORE AS (TABLESPACE "HELLO" ENABLE STORAGE IN "
"ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 65536 FREELIS"
"TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: 遇到 ORACLE 错误 1536
ORA-01536: 超出表空间 'HELLO' 的空间限额
成功终止导入, 但出现警告。
===============================================================================
解决办法,将包括CLOB的列先在目标用户创建表结构
sqlplus jboss/jboss
CREATE TABLE "HELLO" ("NID" INT, "NAME" VARCHAR2(20), "MESSAGE" CLOB);
再执行
imp jboss/jboss fromuser=hello touser=jboss file=hello.dmp log=jboss_exp.log ignore=y
从日志可以看到导入成功,警告可以不用理会
=================================================================================
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
警告: 这些对象由 HELLO 导出, 而不是当前用户
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. . 正在导入表 "HELLO"导入了 2 行
成功终止导入, 没有出现警告。
=================================================================================
5)导入表到相同用户,但表结构不一样,同时默认表空间也不一样的时候
执行前先备份58.251.50.236中用户sms的数据
exp sms/sms123456 file=/home/oracle/sms20101108bak.dmp log=/home/oracle/sms20101108bak.log
revoke unlimited tablespace from sms
alter user sms quota 0 on users;
alter user sms quota unlimited on SMSTBS;
1.先在10.10.100.212备份表receivemessage 时间约2分钟
exp sms/sms@sms file=/home/oracle/sms20101108.dmp log=/home/oracle/sms20101108.log tables=RECEIVEMESSAGE
2.拷贝备份到58.251.50.236 时间约3分钟
scp /home/oracle/sms20101108.dmp 58.251.50.236:/home/oracle/
pwd=admin236ora
3.drop表receivemessage
drop table sms.receivemessage purge;
4.执行导入备份信息
imp sms/sms123456 file=/home/oracle/sms20101108.dmp fromuser=sms touser=sms tables=RECEIVEMESSAGE tablespaces=smstbs
5.修改表结构,添加列node_id
alter table sms.RECEIVEMESSAGE add (NODE_ID NUMBER(5));