一、 实验环境

Sqldeveloper+linuxin virtual machine；

[oracle@lzc~]$ lsb_release -a

LSBVersion: :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

DistributorID: RedHatEnterpriseServer

Description: Red Hat Enterprise Linux Server release 5.4(Tikanga)

Release: 5.4

Codename: Tikanga

数据库版本：

SQL>select * from v$version;

BANNER

----------------------------------------------------------------

OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQLRelease 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNSfor Linux: Version 10.2.0.1.0 - Production

NLSRTLVersion 10.2.0.1.0 - Production

源数据库：db_name：dblzc db_id=dblzcID

目标数据库：db_name：exedb db_id=exedbid

Tnsname 服务名：exedbid

二、 实验目标

把scott这个用户的表导入远程数据库exedb的scott_test用户中，并将scott用户下所有的tables的默认表空间在导入scott_test用户之后改为scott_test用户默认的tablespace。

三、 实验设计

1、在目标数据库中创建测试用户

2、执行第一次导入

3、查看scott_t用户下所有table 的default tablespace

4、清出scott_test 用户下的所有table

5、修改scott_tes用户的权限

6、再次执行导入

7、再次查看scott_t用户下所有table 的default tablespace

8、两次进行对比

9、得出实验结论

实验过程

一、 实验步骤

1. 查看源数据库中scott用户下的所有表的默认表空间。

SQL> select table_name,tablespace_namefrom user_tables;

TABLE_NAME TABLESPACE_NAME

------------------------------------------------------------

DEPT USERS

EMP USERS

BONUS USERS

SALGRADE USERS

STUDENT USERS

STUDENT_INFO USERS

TEST USERS

7 rows selected.

2. 查看元数据库中scott用户的默认存储表空间

SQL> select username,default_tablespacefrom user_users;

USERNAME DEFAULT_TABLESPACE

------------------------------------------------------------

SCOTT USERS

3. 在目标数据库中创建测试用户

SQL> create user scott_test identifiedby oracle default tablespace hr_test;

User created.

4. 为测试用户其授予相关权限

SQL> grant connect,resource toscott_test;

Grant succeeded.

5. 查看创建的测试用户的默认存储表空间

SQL> select username,default_tablespacefrom dba_users;

USERNAME DEFAULT_TABLESPACE

------------------------------------------------------------

SYS SYSTEM

SYSTEM SYSTEM

SYSMAN SYSAUX

。。。。。。。。。。。。。（其余省略）

SCOTT USERS

HR_TEST HR_TEST

SCOTT_TEST HR_TEST

6. 将scott用户下的表导出

[oracle@lzc exp_test]$ exp scott/oraclefile=exp_scott_tables.dmp wner=scott

Export: Release 10.2.0.1.0 - Production onTue Aug 21 13:24:19 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Miningoptions

Export done in US7ASCII character set andAL16UTF16 NCHAR character set

. exporting pre-schema procedural objectsand actions

. exporting foreign function library namesfor user SCOTT

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions foruser SCOTT

About to export SCOTT's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SCOTT's tables viaConventional Path ...

. . exporting table BONUS 0 rows exported

. . exporting table DEPT 4 rows exported

. . exporting table EMP 14 rows exported

. . exporting table SALGRADE 5 rows exported

. . exporting table STUDENT 0 rows exported

. . exporting table STUDENT_INFO 64 rows exported

. . exporting table TEST 6 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrityconstraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional andextensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objectsand actions

. exporting statistics

Export terminated successfully withoutwarnings.

可以看到成功导出！

7. 将scott用户下的表导入scott_test下

[oracle@lzc exp_test]$ impsystem/oracle@exedbid file=exp_scott_tables.dmp fromuser=scotttouser=scott_test buffer=9999

Import: Release 10.2.0.1.0 - Production onTue Aug 21 13:29:40 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Miningoptions

Export file created by EXPORT:V10.02.01 viaconventional path

Warning: the objects were exported bySCOTT, not by you

import done in US7ASCII character set andAL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT_TEST

. . importing table "BONUS" 0 rows imported

. . importing table "DEPT" 4 rows imported

. . importing table "EMP" 14 rows imported

. . importing table "SALGRADE" 5 rows imported

. . importing table "STUDENT" 0 rows imported

. . importing table "STUDENT_INFO" 64 rows imported

. . importing table "TEST" 6 rows imported

About to enable constraints...

Import terminated successfully withoutwarnings.

8. 查看一下是否成功导入

现在我们到目标数据库exedb中查看一下是否成功导入：

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

BONUS

DEPT

EMP

SALGRADE

STUDENT

STUDENT_INFO

TEST

7 rows selected.

可以看到所有表都以成功导入

9. 查看目标数据库中scott_test用户中导入表的默认存储表空间

SQL> select table_name ,tablespace_namefrom user_tables;

TABLE_NAME TABLESPACE_NAME

------------------------------------------------------------

BONUS USERS

DEPT USERS

EMP USERS

SALGRADE USERS

STUDENT USERS

STUDENT_INFO USERS

TEST USERS

7 rows selected.

我们可以看到虽然，scott_test用的默认表空间为hr_test，但从源数据库中导入的表由于其存储属性依然继承原端属性，所以依然存储道理users表空间下。那么我们能不能让这些表在都如的时候就直接导入到scott_test用户默认的表空间hr_test下呢，这就是下面要实验的内容。

10. 将目标数据库scott_test用户下的表删除

在进行第二次导入之前，我们先把上次导入的表全部删除否则再次导入的时候会报对象已存在的错误。

drop table bonus;

drop table emp;

drop table dept;

drop table salgrade;

drop table student;

drop table test;

drop table student_info;

所有表都已删除。

11. 授予scott_test用户相关权限

现在这一步就是我们这个实验中心中的重心了，哈哈，不要错过啊。

先我们就回收scott_test用户unlimitedTablespace的权限（即拥有所有表空间的操作权限），并授予其在hr_test表空的无限操作权限。

SQL> alter user scott_test quotaunlimited on hr_test;

User altered.

SQL> revoke unlimited tablespace fromscott_test;

Revoke succeeded.

12. 第二次执行导入

[oracle@lzc exp_test]$ impsystem/oracle@exedbid file=exp_scott_tables.dmp fromuser=scotttouser=scott_test buffer=9999

Import: Release 10.2.0.1.0 - Production onTue Aug 21 13:41:33 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Miningoptions

Export file created by EXPORT:V10.02.01 viaconventional path

Warning: the objects were exported bySCOTT, not by you

import done in US7ASCII character set andAL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT_TEST

. . importing table "BONUS" 0 rows imported

. . importing table "DEPT" 4 rows imported

. . importing table "EMP" 14 rows imported

. . importing table "SALGRADE" 5 rows imported

. . importing table "STUDENT" 0 rows imported

. . importing table "STUDENT_INFO" 64 rows imported

. . importing table "TEST" 6 rows imported

About to enable constraints...

Import terminated successfully withoutwarnings.

13. 再次查看scott_test用户下所有表的默认表空间

现在我们再次查看，scott_test用户下所有表存储的默认表空间

SQL> select table_name,tablespace_namefrom user_tables;

TABLE_NAME TABLESPACE_NAME

------------------------------------------------------------

BONUS HR_TEST

DEPT HR_TEST

EMP HR_TEST

SALGRADE HR_TEST

STUDENT HR_TEST

STUDENT_INFO HR_TEST

TEST HR_TEST

7 rows selected.

哈哈，看到了吧，你的目标实现了吗？

二、 实验分析

三、 实验小结

使用import导入数据时会首先创建表结构，这个表结构肯定来自于export导出的数据库，所以其存储属性也肯定是源端数据库中的存储属性，那么在import命令在目标数据库创建表时，存储属性依然继承源端数据库中的存储属性。

这样在导入数据时，数据在源端存储在哪个表空间，在目标数据库默认仍会寻找并存储到那个表空间。

如果把目标数用户的unlimitedTablespace（所有表空间的操作权限）权限收回，这样虽然能找的到但也无法导入进去了。