« 使用Rman进行不完全恢复 | Blog首页 | 使用Oracle10g新特性,跨越Resetlogs时间点进行恢复 »
如何把数据导入不同的表空间?
作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】链接：http://www.eygle.com/archives/2005/04/ecineeeeiaeioae.html
很多人在进行数据迁移时，希望把数据导入不同于原系统的表空间，在导入之后却往往发现，数据被导入了原表空间。
本例举例说明解决这个问题:
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.
现在数据被导入到正确的用户表空间中. -----
这篇 【如何把数据导入不同的表空间?】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key
By eygle on 2005-04-05 14:52 | Comments (10) | Posted to Backup&Recovery | Edit |Pageviews:
Listed below are links to weblogs that reference 如何把数据导入不同的表空间?:
» Oracle初学者入门指南-系统与用户数据分离 from Friends Life and Oracle
我们知道,在Oracle10g中,Oracle提供了缺省数据表空间的概念,即使用户不指定,Oracle也不会将用户数据存放在SYSTEM表空间,而且,Oracle还将一些无关紧要的系统数据分离到SYSAUX表空间,以使得SYSTEM表空间纯净化. [Read More]
Tracked on September 2, 2006 10:05 PM
留言 (10)
嗯，我就出现过这样的问题，谢谢PH
Posted by: booby at April 5, 2005 3:02 PM
参考我的这篇，也总结过这样的问题：
http://blog.csdn.net/kamus/archive/2004/06/10/22282.aspx
Posted by: kamus at April 6, 2005 1:14 AM
有人问到这个问题才贴出来的，本来没什么技术含量。
Posted by: eygle at April 6, 2005 9:21 AM
有时候这个也不好使。我今天导入一个分区表的时候就遇到这个问题，最后还是先在目标库建立表结构，然后再导数据进去的。
Posted by: suk at December 9, 2005 6:18 PM
试了怎么不行啊，报错
原来从users表空间中导出
现在想导入到自定义的DATA中
如上操作导入时报在users表空间的权限不够
Posted by: torrent at August 7, 2006 9:04 PM
报什么错？你的步骤？
有授权么？
Posted by: eygle at August 7, 2006 9:13 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行被导入
Posted by: donnieyen at May 25, 2007 7:25 PM
你的这个表创建时强制指定了表空间和LOB对象存储表空间，这在imp时是没办法切换的，你需要手工来创建这个对象，再单独导入这个表就可以了。
Posted by: eygle at May 26, 2007 11:01 AM
SQL> revoke unlimited tablespace from test;
撤销成功。
SQL> alter user test quota 0 on users;
用户已更改。
SQL> alter user test quota unlimited on test;
用户已更改。
这几句是什么意思啊，为什么加了这几句，就可以导到其他的标空间了呢？
Posted by: Anonymous at May 31, 2007 2:49 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能否解释一下啊。
Posted by: seamanczg1984 at May 31, 2007 2:58 PM
