熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

如何把數據導入不同的表空間?

2013-11-13 22:13:53  來源: Oracle 

  很多人在進行數據遷移時希望把數據導入不同於原系統的表空間在導入之後卻往往發現數據被導入了原表空間
  
  本例舉例說明解決這個問題:
  
  如果缺省的用戶具有DBA權限
  
  那麼導入時會按照原來的位置導入數據即導入到原表空間
  $ imp bjbbs/passwd file=bj_bbsdmp fromuser=jive touser=bjbbs grants=n
  
  Import: Release Production on Mon Sep ::
  
  (c) Copyright Oracle Corporation All rights reserved
  
  Connected to: Oraclei Enterprise Edition Release bit Production
  With the Partitioning option
  JServer Release bit Production
  
  Export file created by EXPORT:V via conventional path
  
  Warning: the objects were exported by JIVE not by you
  
  import done in ZHSGBK character set and ZHSGBK NCHAR character set
   importing table        HS_ALBUMINBOX     rows imported
   importing table        HS_ALBUM_INFO     rows imported
   importing table          HS_CATALOG     rows imported
   importing table     HS_CATALOGAUTHORITY      rows imported
   importing table     HS_CATEGORYAUTHORITY      rows imported
  
   importing table         JIVEUSERPROP      rows imported
   importing table          JIVEWATCH      rows imported
   importing table          PLAN_TABLE      rows imported
   importing table          TMZOLDUSER      rows imported
   importing table         TMZOLDUSER      rows imported
  About to enable constraints
  Import terminated successfully without warnings
  
  查詢發現仍然導入了USER表空間
  
  $ sqlplus bjbbs/passwd
  
  SQL*Plus: Release Production on Mon Sep ::
  
  (c) Copyright Oracle Corporation All rights reserved
  
  Connected to:
  Oraclei Enterprise Edition Release bit Production
  With the Partitioning option
  JServer Release bit Production
  
  SQL> select table_nametablespace_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
  
  TMZOLDUSER          USERS
  
   rows selected
  
  回收用戶unlimited tablespace權限
  
  這樣就可以導入到用戶缺省表空間
  
  SQL> create user bjbbs identified by passwd
    default tablespace bjbbs
    temporary tablespace temp
    /
  
  User created
  
  SQL> grant connectresource to bjbbs;
  
  Grant succeeded
  
  SQL> grant dba to bjbbs;
  
  Grant succeeded
  
  SQL> revoke unlimited tablespace from bjbbs;
  
  Revoke succeeded
  
  SQL> alter user bjbbs quota on users;
  
  User altered
  
  SQL> alter user bjbbs quota unlimited on bjbbs;
  
  User altered
  
  SQL> exit
  Disconnected from Oraclei Enterprise Edition Release bit Production
  With the Partitioning option
  JServer Release bit Production
  
  重新導入數據
  
  $ imp bjbbs/passwd file=bj_bbsdmp fromuser=jive touser=bjbbs grants=n
  
  Import: Release Production on Mon Sep ::
  
  (c) Copyright Oracle Corporation All rights reserved
  
  Connected to: Oraclei Enterprise Edition Release bit Production
  With the Partitioning option
  JServer Release bit Production
  
  Export file created by EXPORT:V via conventional path
  
  Warning: the objects were exported by JIVE not by you
  
  import done in ZHSGBK character set and ZHSGBK NCHAR character set
   importing table        HS_ALBUMINBOX     rows imported
   importing table        HS_ALBUM_INFO     rows imported
   importing table          HS_CATALOG     rows imported
   importing table     HS_CATALOGAUTHORITY      rows imported
   importing table     HS_CATEGORYAUTHORITY      rows imported
   importing table       HS_CATEGORYINFO      rows imported
   importing table        HS_DLF_DOWNLOG      rows imported
  
   importing table           JIVEUSER     rows imported
   importing table         JIVEUSERPERM     rows imported
   importing table         JIVEUSERPROP      rows imported
   importing table          JIVEWATCH      rows imported
   importing table          PLAN_TABLE      rows imported
   importing table          TMZOLDUSER      rows imported
   importing table         TMZOLDUSER      rows imported
  About to enable constraints
  Import terminated successfully without warnings
  
  SQL> select table_nametablespace_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
  
  TMZOLDUSER          BJBBS
  
   rows selected
  
  現在數據被導入到正確的用戶表空間中
From:http://tw.wingwit.com/Article/program/Oracle/201311/18548.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.