如何把数据导入不同的表空间?
很多人在进行数据迁移时，希望把数据导入不同于原系统的表空间，在导入之后却往往发现，数据被导入了原表空间。本例举例说明解决这个问题:
1.如果缺省的用户具有DBA权限
那么导入时会按照原来的位置导入数据，即导入到原表空间
$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=nImport: 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 ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - 64bit ProductionExport file created by EXPORT:V08.01.07 via conventional pathWarning: the objects were exported by JIVE, not by youimport 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 importedAbout to enable constraints...Import terminated successfully without warnings.
查询发现仍然导入了USER表空间
$ sqlplus bjbbs/passwdSQL*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 ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - 64bit ProductionSQL> select table_name,tablespace_name from user_tables;TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------HS_ALBUMINBOX USERSHS_ALBUM_INFO USERSHS_CATALOG USERSHS_CATALOGAUTHORITY USERSHS_CATEGORYAUTHORITY USERSHS_CATEGORYINFO USERSHS_DLF_DOWNLOG USERS...JIVEWATCH USERSPLAN_TABLE USERSTMZOLDUSER USERSTABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------TMZOLDUSER2 USERS45 rows selected.2.回收用户unlimited tablespace权限
这样就可以导入到用户缺省表空间
SQL> create user bjbbs identified by passwd2default tablespace bjbbs3temporary tablespace temp4/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> exitDisconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - 64bit Production
重新导入数据
$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=nImport: 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 ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - 64bit ProductionExport file created by EXPORT:V08.01.07 via conventional pathWarning: the objects were exported by JIVE, not by youimport 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 importedAbout to enable constraints...Import terminated successfully without warnings.SQL> select table_name,tablespace_name from user_tables;TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------HS_ALBUMINBOX BJBBSHS_ALBUM_INFO BJBBSHS_CATALOG BJBBSHS_CATALOGAUTHORITY BJBBS....JIVETHREAD BJBBSJIVETHREADPROP BJBBSJIVEUSER BJBBSJIVEUSERPERM BJBBSJIVEUSERPROP BJBBSJIVEWATCH BJBBSPLAN_TABLE BJBBSTMZOLDUSER BJBBSTABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------TMZOLDUSER2 BJBBS45 rows selected.
现在数据被导入到正确的用户表空间中.-----
(原为)http://www.eygle.com/archives/2005/04/ecineeeeiaeioae.html
页:
[1]