如何把数据导入不同的表空间?

« 使用Rman进行不完全恢复 | Blog首页 | 使用Oracle10g新特性,跨越Resetlogs时间点进行恢复 »

很多人在进行数据迁移时，希望把数据导入不同于原系统的表空间，在导入之后却往往发现，数据被导入了原表空间。
本例举例说明解决这个问题:
1.如果缺省的用户具有DBA权限
那么导入时会按照原来的位置导入数据，即导入到原表空间

$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=n

Import: Release 8.1.7.4.0 - Production on Mon Sep 22 11:49:41 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by JIVE, not by you

import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. . importing table                "HS_ALBUMINBOX"         12 rows imported
. . importing table                "HS_ALBUM_INFO"         47 rows imported
. . importing table                   "HS_CATALOG"         13 rows imported
. . importing table          "HS_CATALOGAUTHORITY"          5 rows imported
. . importing table         "HS_CATEGORYAUTHORITY"          0 rows imported
....
. . importing table                 "JIVEUSERPROP"          4 rows imported
. . importing table                    "JIVEWATCH"          0 rows imported
. . importing table                   "PLAN_TABLE"          0 rows imported
. . importing table                   "TMZOLDUSER"          3 rows imported
. . importing table                  "TMZOLDUSER2"          3 rows imported
About to enable constraints...
Import terminated successfully without warnings.

查询发现仍然导入了USER表空间

$ sqlplus bjbbs/passwd

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 22 11:50:03 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
HS_ALBUMINBOX                  USERS
HS_ALBUM_INFO                  USERS
HS_CATALOG                     USERS
HS_CATALOGAUTHORITY            USERS
HS_CATEGORYAUTHORITY           USERS
HS_CATEGORYINFO                USERS
HS_DLF_DOWNLOG                 USERS
...
JIVEWATCH                      USERS
PLAN_TABLE                     USERS
TMZOLDUSER                     USERS

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TMZOLDUSER2                    USERS

45 rows selected.

2.回收用户unlimited tablespace权限
这样就可以导入到用户缺省表空间

SQL> create user bjbbs identified by passwd
  2  default tablespace bjbbs
  3  temporary tablespace temp
  4  /

User created.


SQL> grant connect,resource to bjbbs;

Grant succeeded.

SQL> grant dba to bjbbs;

Grant succeeded.

SQL> revoke unlimited tablespace from bjbbs;

Revoke succeeded.

SQL> alter user bjbbs quota 0 on users;

User altered.

SQL> alter user bjbbs quota unlimited on bjbbs;

User altered.

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

重新导入数据

$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=n

Import: Release 8.1.7.4.0 - Production on Mon Sep 22 12:00:51 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by JIVE, not by you

import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. . importing table                "HS_ALBUMINBOX"         12 rows imported
. . importing table                "HS_ALBUM_INFO"         47 rows imported
. . importing table                   "HS_CATALOG"         13 rows imported
. . importing table          "HS_CATALOGAUTHORITY"          5 rows imported
. . importing table         "HS_CATEGORYAUTHORITY"          0 rows imported
. . importing table              "HS_CATEGORYINFO"          9 rows imported
. . importing table               "HS_DLF_DOWNLOG"          0 rows imported
....
. . importing table                     "JIVEUSER"        102 rows imported
. . importing table                 "JIVEUSERPERM"         81 rows imported
. . importing table                 "JIVEUSERPROP"          4 rows imported
. . importing table                    "JIVEWATCH"          0 rows imported
. . importing table                   "PLAN_TABLE"          0 rows imported
. . importing table                   "TMZOLDUSER"          3 rows imported
. . importing table                  "TMZOLDUSER2"          3 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
HS_ALBUMINBOX                  BJBBS
HS_ALBUM_INFO                  BJBBS
HS_CATALOG                     BJBBS
HS_CATALOGAUTHORITY            BJBBS
....
JIVETHREAD                     BJBBS
JIVETHREADPROP                 BJBBS
JIVEUSER                       BJBBS
JIVEUSERPERM                   BJBBS
JIVEUSERPROP                   BJBBS
JIVEWATCH                      BJBBS
PLAN_TABLE                     BJBBS
TMZOLDUSER                     BJBBS

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TMZOLDUSER2                    BJBBS

45 rows selected.

现在数据被导入到正确的用户表空间中.
历史上的今天...
>> 2009-04-05文章:

关于Oracle的Dual表

>> 2006-04-05文章:

Oracle10g:v$session_wait_history视图

>> 2004-04-05文章:

进京一周年记-eygle在北京的生活之五

By eygle on 2005-04-05 14:52 | Comments (17) | Backup&Recovery | 242 |

17 Comments

booby | April 5, 2005 3:02 PM

嗯，我就出现过这样的问题，谢谢PH

kamus | April 6, 2005 1:14 AM

参考我的这篇，也总结过这样的问题：
http://blog.csdn.net/kamus/archive/2004/06/10/22282.aspx

eygle | April 6, 2005 9:21 AM

有人问到这个问题才贴出来的，本来没什么技术含量。

suk | December 9, 2005 6:18 PM

有时候这个也不好使。我今天导入一个分区表的时候就遇到这个问题，最后还是先在目标库建立表结构，然后再导数据进去的。

torrent | August 7, 2006 9:04 PM

试了怎么不行啊，报错
原来从users表空间中导出
现在想导入到自定义的DATA中
如上操作导入时报在users表空间的权限不够

eygle | August 7, 2006 9:13 PM

报什么错？你的步骤？
有授权么？

donnieyen | May 25, 2007 7:25 PM

我下午在测试的时候,就报错.步骤是按照你上面的步骤做的.
SQL> create tablespace test datafile 'e:\oracle\oradata\dsoa\test.dbf' size 500M
;
表空间已创建。
SQL> create temporary tablespace test_temp tempfile 'e:\oracle\oradata\dsoa\test
_temp.dbf' size 100M;

表空间已创建。
SQL> create user test identified by test default tablespace test
2 temporary tablespace test_temp;

用户已创建

SQL> grant connect ,resource to test;

授权成功。

SQL> grant dba to test;

授权成功。

SQL> revoke unlimited tablespace from test;

撤销成功。

SQL> alter user test quota 0 on users;

用户已更改。

SQL> alter user test quota unlimited on test;

用户已更改。
连接到: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

经由直接路径导出由EXPORT:V09.02.00创建的导出文件

警告: 此对象由 DSOA 导出, 而不是当前用户

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. . 正在导入表 "ADDRESSLISTCLASS" 6行被导入
. . 正在导入表 "ADDRESSLISTCLASS_RECYCLE" 0行被导入
. . 正在导入表 "ADDRESSLISTDEPTANDUSERINFO" 2行被导入
. . 正在导入表 "ADDRESSLISTINFO" 4行被导入
. . 正在导入表 "ADDRESSLIST_MAXVALUE" 1行被导入
. . 正在导入表 "ADD_PERSONAL" 3行被导入
. . 正在导入表 "AGENDA" 27行被导入
. . 正在导入表 "AGENDA_DAYS" 8行被导入
. . 正在导入表 "AGENDA_MEMO" 0行被导入
. . 正在导入表 "AGENDA_RSC" 0行被导入
. . 正在导入表 "AGENDA_USER" 27行被导入
IMP-00017: 由于 ORACLE 的 1536 错误，以下的语句失败
"CREATE TABLE "ARTICLE" ("NEWSID" NUMBER(10, 0) NOT NULL ENABLE, "TITLE" VAR"
"CHAR2(500), "N_FNAME" VARCHAR2(400), "PATH" VARCHAR2(400), "CONTENT" CLOB, "
""TYPEID" NUMBER(10, 0), "TYPENAME" VARCHAR2(400), "NFROM" VARCHAR2(400), "S"
"HENHE" NUMBER(10, 0), "ABOUT" VARCHAR2(400), "HITS" NUMBER(10, 0), "PICURL""
" VARCHAR2(400), "DATEANDTIME" DATE, "UNAME" VARCHAR2(400), "LOGNAME" VARCHA"
"R2(400), "ZTID" NUMBER(5, 0), "SHENHEREN" VARCHAR2(200), "WEBLANMU" VARCHAR"
"2(400), "URL" VARCHAR2(400), "PICNEWS" NUMBER(5, 0), "SHENHEYIJIAN" CLOB, ""
"QIXIAN" DATE, "ABBYTES" NUMBER(10, 0), "SHENHEUNAME" VARCHAR2(400), "ISPOPU"
"P" NUMBER(10, 0), "QISHU" NUMBER(8, 0), "ZQISHU" NUMBER(10, 0), "PREVIEW" V"
"ARCHAR2(1000), "ISTOP" NUMBER(10, 0) NOT NULL ENABLE, "ISLEADER" NUMBER(10,"
" 0) NOT NULL ENABLE, "NIANDU" NUMBER(4, 0), "WH" VARCHAR2(80), "REDHEAD" VA"
"RCHAR2(3)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1"
"6384 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS L"
"OB ("CONTENT") STORE AS (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 81"
"92 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1 FREELIS"
"T GROUPS 1)) LOB ("SHENHEYIJIAN") STORE AS (TABLESPACE "USERS" ENABLE STOR"
"AGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 65536 "
"FREELISTS 1 FREELIST GROUPS 1))"
IMP-00003: 遇到 ORACLE 错误 1536
ORA-01536: 超出表空间'USERS'的空间限量
. . 正在导入表 "ARTICLE_LOG" 604行被导入
. . 正在导入表 "BBS" 0行被导入
. . 正在导入表 "BBS_ISSUE" 0行被导入

eygle | May 26, 2007 11:01 AM

你的这个表创建时强制指定了表空间和LOB对象存储表空间，这在imp时是没办法切换的，你需要手工来创建这个对象，再单独导入这个表就可以了。

Anonymous | May 31, 2007 2:49 PM

SQL> revoke unlimited tablespace from test;

撤销成功。

SQL> alter user test quota 0 on users;

用户已更改。

SQL> alter user test quota unlimited on test;

用户已更改。

这几句是什么意思啊，为什么加了这几句，就可以导到其他的标空间了呢？

seamanczg1984 | May 31, 2007 2:58 PM

SQL> revoke unlimited tablespace from bjbbs;

Revoke succeeded.

SQL> alter user bjbbs quota 0 on users;

User altered.

SQL> alter user bjbbs quota unlimited on bjbbs;

User altered.

这几句话什么意思，EYGLE能否解释一下啊。

liuliu | November 3, 2008 5:02 PM

谢谢大师，偶已经解决了在这个问题！

Edward | November 23, 2008 1:35 PM

使用Impdb导入数据提示如下错误：
ORA-31655: 尚未为作业选择数据或元数据对象
ORA-39154: 外部方案中的对象已从导入中删除
首先用expdb成功导出数据
C:\Documents and Settings\Administrator>expdp edward/edward directory=dump_test
dumpFile=edward1.dmp parallel=2
而后用impdp导入数据提示上面的错误
C:\Documents and Settings\Administrator>impdp petter/petter directory=dump_test
dumpfile=edward1.dmp REMAP_TABLESPACE=edward:petter
我想把导出后的内容导入到petter表空间中

Edward | November 23, 2008 1:38 PM

如果导入的命令改成impdp edward/edwarddirectory=dump_test
dumpfile=edward1.dmp REMAP_TABLESPACE=edward:petter
执行后是往源表空间导入，而不是目标表空间

yanghy | May 7, 2009 9:39 PM

你的这个表创建时强制指定了表空间和LOB对象存储表空间，这在imp时是没办法切换的，你需要手工来创建这个对象，再单独导入这个表就可以了。

请问这个问题具体怎么解决的？

eygle | May 7, 2009 10:10 PM

imp 指定 indexfile参数，获得建表语句，执行之后，再导入表数据，只能麻烦一点了：）

wangliang | May 8, 2009 10:42 AM

用PLSQL Developer，tool选项单独倒出这个表结构，修改后，倒入；简单点。

eygle | May 8, 2009 1:07 PM

PL/SQL Developer比较好用，但是通常会有版权问题，推荐Oracle的SQL Developer.