ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 把数据导入到不同的表空间

把数据导入到不同的表空间

原创 Linux操作系统 作者:lsm_3036 时间:2011-04-07 11:22:45 0 删除 编辑
    很多人在进行数据迁移时,希望把数据导入不同于原系统的表空间,在导入之后却往往发现,数据被导入了原表空间。
  
  本例举例说明解决这个问题:
  
  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.
  
  现在数据被导入到正确的用户表空间中.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16978544/viewspace-691850/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    64
  • 访问量
    139675