Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2574040
  • 博文数量: 323
  • 博客积分: 10211
  • 博客等级: 上将
  • 技术积分: 4934
  • 用 户 组: 普通用户
  • 注册时间: 2006-08-27 14:56
文章分类

全部博文(323)

文章存档

2012年(5)

2011年(3)

2010年(6)

2009年(140)

2008年(169)

分类: Oracle

2009-03-26 17:02:26

目的:将sfx schema下的所有对象导入到sjh schema下,注意用户sfx,sjh所在的默认表空间不一样。
 
$ exp sfx/sfx file=sfx.dmp
Export: Release 10.2.0.2.0 - Production on Thu Mar 26 16:26:35 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SFX
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SFX
About to export SFX's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SFX's tables via Conventional Path ...
. . exporting table                             T1          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
 
$ imp sjh/sjh file=sfx.dmp fromuser=sfx touser=sjh
Import: Release 10.2.0.2.0 - Production on Thu Mar 26 16:40:52 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SFX, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . importing table                           "T1"          1 rows imported
Import terminated successfully without warnings.

SQL> show user
USER is "SJH"
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SJH0                           USERS
T1                             SJHTEST
TEST                           USERS
TEST1                          USERS
SQL> select * from T1;
        ID NAME
---------- --------
         1 sjh

--表空间还是原来的SJHTEST。达不到我们的目的。
 
对用户SJH做些设置:
 
SQL> grant dba to sjh;
Grant succeeded.
SQL> revoke unlimited tablespace from sjh;
Revoke succeeded.
SQL> alter user sjh quota 0 on SJHTEST;
User altered.
SQL> alter user sjh quota unlimited on USERS;
User altered.
 
重新开始导入:
SQL> drop table T1 purge;
Table dropped.
$ imp sjh/sjh file=sfx.dmp fromuser=sfx touser=sjh
Import: Release 10.2.0.2.0 - Production on Thu Mar 26 16:59:36 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SFX, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . importing table                           "T1"          1 rows imported
Import terminated successfully without warnings.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SJH0                           USERS
T1                             USERS
TEST                           USERS
TEST1                          USERS
SQL> select * from t1;
        ID NAME
---------- --------
         1 sjh
 
OK!
 
阅读(841) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~