eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 使用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文章:
    >> 2004-04-05文章:

无觅

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

17 Comments

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

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

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

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

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

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

我下午在测试的时候,就报错.步骤是按照你上面的步骤做的.
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行被导入

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

SQL> revoke unlimited tablespace from test;

撤销成功。

SQL> alter user test quota 0 on users;

用户已更改。

SQL> alter user test quota unlimited on test;

用户已更改。

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

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能否解释一下啊。

谢谢大师,偶已经解决了在这个问题!

使用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表空间中

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

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

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

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

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

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


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com