exp,imp的问题？

显示全部楼层 · 2003-3-27 17:25:00

想升级数据库，我已经在9i 的数据库里事先建好了所需要的表空间。首先导入用户：
nt4.0+oracle8.1.7用以下命令exp system/manager@oracle1 full=y file=d:\fulln.dmp rows=n .
nt4.0+oracle9.2.0.1.0用以下命令imp system/manager@oracle2 full=y file=d:\fulln.dmp rows=n ignore=y
出现错误如下：
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
export server uses US7ASCII NCHAR character set (possible ncharset conversion)
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "USER_DATA" DATAFILE'O:\ORADATA\ORCL\USR1ORCL.DBF' SIZE"
" 655360000 AUTOEXTEND ON NEXT 1310720MAXSIZE 8191M, 'O:\ORADATA\ORC"
"L\USR4ORCL.ORA' SIZE 1048576000, 'O:\ORADATA\ORCL\USR2ORCL.ORA' SIZE "
"524288000, 'O:\ORADATA\ORCL\USR3ORCL.ORA' SIZE 524288000 DEFAUL"
"TSTORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCRE"
"ASE 0) ONLINE PERMANENTEXTENT MANAGEMENT DICTIONARY"
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "TOOLS" DATAFILE'O:\ORADATA\ORCL\TOOLS01.DBF' SIZE 1048"
"57600 AUTOEXTEND ON NEXT 327680MAXSIZE 8191M DEFAULTSTORAGE(INITI"
"AL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0) ONLINE PERM"
"ANENTEXTENT MANAGEMENT DICTIONARY"
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "INDX" DATAFILE'O:\ORADATA\ORCL\INDX01.DBF' SIZE 608174"
"08 AUTOEXTEND ON NEXT 1310720MAXSIZE 8191M DEFAULTSTORAGE(INITIAL"
" 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0) ONLINE PERM"
"ANENTEXTENT MANAGEMENT DICTIONARY"
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "ROLLBACK_DATA" DATAFILE'O:\ORADATA\ORCL\RBS1ORCL.DBF' "
"SIZE 524288000 DEFAULTSTORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1"
" MAXEXTENTS 121 PCTINCREASE 50) ONLINE PERMANENTEXTENT MANAGEMENT DICTION"
"ARY"
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "TEMPORARY_DATA" DATAFILE'O:\ORADATA\ORCL\TMP1ORCL_DATA"
".DBF' SIZE 524288000 DEFAULTSTORAGE(INITIAL 10240 NEXT 10240 MINEXT"
"ENTS 1 MAXEXTENTS 121 PCTINCREASE 50) ONLINE TEMPORARYEXTENT MANAGEMENT D"
"ICTIONARY"......

千问 · 2003-3-27 17:25:00

千问 · 2003-3-27 17:25:00

9i和8i有很多系统表都不一样了，你这样把8i的数据字典倒入9i应该是行不通的。为什么不只倒入用户数据呢？

千问 · 2003-3-27 17:25:00

最初由 maggie 发布
[B]9i和8i有很多系统表都不一样了，你这样把8i的数据字典倒入9i应该是行不通的。为什么不只倒入用户数据呢？ [/B]
我怎样把我愿来数据库建的用户导到9i上来呢？

千问 · 2003-3-27 17:25:00

在exp时使用OWNER参数指定模式导出。相应的用户是能够和它的模式一起倒入新库的。

千问 · 2003-3-27 17:25:00

最初由 maggie 发布
[B]在exp时使用OWNER参数指定模式导出。相应的用户是能够和它的模式一起倒入新库的。 [/B]
200个用户我一个个输入吗？

千问 · 2003-3-27 17:25:00

如果是用户多表不多的话，用tables方式导出表
用select 'create user '||username|| '...' from dba_users; 方式建用户吧

千问 · 2003-3-27 17:25:00

9I里面好像已经取消了EXP功能了吧!!不知道是不是~

千问 · 2003-3-27 17:25:00

我觉得好像以上的错误都不影响创建用户吧，它只是提示无法创建tablespace

千问 · 2003-3-27 17:25:00

最初由 Seraphim 发布
[B]看到错误了吗？ORA-12913: Cannot create dictionary managed tablespace ，说得很明白啊，不能创建dictionary managed tablespace ，9i里面SYSTM表空间是locally managed的，所以你的创建失败了。把full=Y去掉，然后单独导入用户数据。
ORA-12913 Cannot create dictionary managed tablespace
Cause: An attempt was made to create a dictionary managed tablespace in a database whose SYSTEM tablespace is locally managed.
Action: Create a locally managed tablespace. [/B]
我有200用户，有没有好的方法导入?一个个建太麻烦了。