oracle如何把数据导入不同的表空间
来源:互联网 发布:淘宝产品二维码生成 编辑:程序博客网 时间:2024/05/11 05:16
很多人在进行数据迁移时,希望把数据导入不同于原系统的表空间,在导入之后却往往发现,数据被导入了原表空间。
本例举例说明解决这个问题:
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 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> 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.
- oracle如何把数据导入不同的表空间
- Oracle 把数据导入不同的表空间
- 如何把数据导入不同的表空间?
- 如何把数据导入不同的表空间?
- 如何把数据导入不同的表空间
- 如何把数据导入不同的表空间?
- 如何把数据导入不同的表空间?
- 如何把数据导入不同的表空间?
- 如何把数据导入不同的表空间?
- 把数据导入不同的表空间(exp,imp)
- 把数据导入不同的表空间(exp,imp)
- oracle导入dmp数据,不同表空间
- ORACLE把数据导入不同于原系统的表空间
- oracle 数据导入不同的表空间-dmp文件
- oracle不同表空间的导入
- oracle不同表空间的导入
- oracle通过impdp导入不同表用户、不同表空间的数据
- oracle通过impdp导入不同表用户、不同表空间的数据
- 高效利用时间的5个小方法
- 学习PHP重在坚持
- 今天解决了 项目中已经存在对组件“XXX”的引用 的小问题
- 《Moblin非官方教程》系列连载 第二章--第二节--GCC编译器和GDB调试器
- 30个Oracle语句优化规则详解
- oracle如何把数据导入不同的表空间
- vs.net 无法打开Web项目 -网站编程
- 让Windows Mobile 6.1的手机可以运行控制台应用程序
- 占个地先
- Oracle相关权限
- SQL优化之-In和Exists
- Delphi指针备忘
- 多态性----vptr----vtable
- SqlTransaction 类