今天在研究了一下关于ORACLE的导入导出的功能，周五快要下班的时候给同事新建一个表空间，将同一个数据库中的某个用户下的表导入新的表空间上，建好表空间和用户后，直接用 exp ,imp 来导入数据到新的表空间，查是发现后来导入的数据还在原来的表空间上，（周五那天的我是用工具编辑DMP文件，修改里的表空间名后导入的），后来想起来了eygle的书上写了关于这个的问题，于是今天就来试验了一下。
第一种：修改用户的一些权限。
首先使用sytem帐户登陆
--创建新表空间的用户
SQL> create user pangzi identified by pangzi default tablespace pangzi temporary tablespace temp;
用户已创建。
--授给新用户一般的权限
SQL> grant export full database to pangzi;
授权成功。
SQL> grant import full database to pangzi;
授权成功。
SQL> grant connect,resource to pangzi;
授权成功。
SQL> grant create procedure to pangzi;
授权成功。
SQL> grant create job to pangzi;
授权成功。
SQL> grant create view to pangzi;
授权成功。
SQL> grant create synonym to pangzi;
授权成功。
--从这里开始将是增加的，为了使导入的数据，不放在原来的表空间上
SQL> grant dba to pangzi;
--收回用户的umlimited tablespace权限
SQL> revoke unlimited tablespace from pangzi;
--设置新创建的用户可使用syb表空间的大小0（原数据所在的表空间为syb）
SQL> alter user pangzi quota 0 on syb;
--设置新创建的用户可使用pangzi表空间的大小不限制
SQL> alter user pangzi quota unlimited on pangzi;
--收回DBA权限
SQL> revoke dba from pangzi;
执行导入数据
C:\Users\dyspangzi>imp pangzi/pangzi@testdev file=syb.dmp full=y
Import: Release 10.2.0.3.0 - Production on 星期六 4月 21 17:48:54 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
警告: 这些对象由 SYB 导出, 而不是当前用户
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 SYB 的对象导入到 PANGZI
. . 正在导入表 "ALL_SALES"导入了 360 行
. . 正在导入表 "COUPONS"导入了 6 行
. . 正在导入表 "CUSTOMERS"导入了 5 行
. .中间部分内容省略，都是导入的信息
. . 正在导入表 "REG_EXPS"导入了 1 行
. . 正在导入表 "SALARY_GRADES"导入了 4 行
即将启用约束条件...
成功终止导入, 没有出现警告。
导入成功，我们看导的表已经在新的表空间中了
SQL> show user
USER 为 "PANGZI"
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DYSPANGZI PANGZI
CUSTOMERS PANGZI
PRODUCT_TYPES PANGZI
PRODUCTS PANGZI
PURCHASES PANGZI
EMPLOYEES PANGZI
SALARY_GRADES PANGZI
PURCHASES_WITH_TIMESTAMP PANGZI
PURCHASES_TIMESTAMP_WITH_TZ PANGZI
PURCHASES_WITH_LOCAL_TZ PANGZI
COUPONS PANGZI
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PROMOTIONS PANGZI
ORDER_STATUS PANGZI
PRODUCT_CHANGES PANGZI
MORE_PRODUCTS PANGZI
MORE_EMPLOYEES PANGZI
DIVISIONS PANGZI
JOBS PANGZI
EMPLOYEES2 PANGZI
ALL_SALES PANGZI
PRODUCT_PRICE_AUDIT PANGZI
REG_EXPS PANGZI
已选择22行。
第二种：使用EXPDP和IMPDP来导入和导出
使用expdp来导出，首先要为一个参数来指定一个路径——directory，expdp是在服务器端工作，导出的文件需要放在本地目录，这个参数就是保存导出文件的位置。这个可以自己创建，也可以是默认的，我自己创建了一个，名字是expdir
SQL> CREATE OR REPLACE DIRECTORY expdir AS '/var/backup';
目录已创建。
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------- ------------------------------- -----------------------------------------
SYS ADMIN_DIR /ade/aime_10.2_lnx_push/oracle/md/admin
SYS DATA_PUMP_DIR /usr/app/oracle/product/10.2.0/db_1/rdbms/log/
SYS WORK_DIR /ade/aime_10.2_lnx_push/oracle/work
SYS EXPDIR /var/backup
--给用户授予读写权限
SQL> grant read,write on directory expdir to syb;
开始导出数据
[oracle@dyspangzi ~]$ expdp dumpfile=syb.dmp directory=expdir;
Export: Release 10.2.0.1.0 - Production on Saturday, 21 April, 2012 18:06:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYB"."SYS_EXPORT_SCHEMA_01": dumpfile=syb.dmp directory=expdir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.375 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "SYB"."DYSPANGZI" 48.50 KB 659 rows
. . exported "SYB"."ALL_SALES" 13.68 KB 360 rows
中间导出信息省略
. . exported "SYB"."REG_EXPS" 5.437 KB 1 rows
. . exported "SYB"."SALARY_GRADES" 5.710 KB 4 rows
. . exported "SYB"."PRODUCT_PRICE_AUDIT" 0 KB 0 rows
Master table "SYB"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYB.SYS_EXPORT_SCHEMA_01 is:
/var/backup/syb.dmp
Job "SYB"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:07:11
导出成功后查看一下刚才创建目录里边的内容
[root@dyspangzi var]# cd backup
[root@dyspangzi backup]# ls -l
总计 720
-rw-r--r-- 1 oracle oinstall 3472 04-21 18:07 export.log
-rw-r----- 1 oracle oinstall 729088 04-21 18:07 syb.dmp
多了两个文件，一个是日志文件一个是导出的数据文件，在导入的时候需要这两个文件。下面开始导入。
===================================================================================
出现了第一个错误
[oracle@dyspangzi ~]$ impdp pangzi/pangzi dumpfile=syb.dmp directory=expdir remap_tablespace=syb:pangzi
Import: Release 10.2.0.1.0 - Production on Saturday, 21 April, 2012 18:45:35
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name EXPDIR is invalid
出现了错误，后来发现这个是因为新用户pangzi没有对目录的读写权限造成的，于是加上权限
SQL> grant read,write on directory expdir to pangzi;
授权成功。
==================================================================================
出现了第二个错误
[oracle@dyspangzi ~]$ impdp pangzi/pangzi dumpfile=syb.dmp directory=expdir remap_tablespace=syb:pangzi
Import: Release 10.2.0.1.0 - Production on Saturday, 21 April, 2012 18:47:38
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "PANGZI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PANGZI"."SYS_IMPORT_FULL_01": pangzi/******** dumpfile=syb.dmp directory=expdir remap_tablespace=syb:pangzi
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "PANGZI"."SYS_IMPORT_FULL_01" successfully completed at 18:47:41
这个是由于原来的用户和现在的不一样了，加上这个参数就好了 remap_schema=syb:pangzi
=====================================================================================================
下面是没有任何错误的导入了
[oracle@dyspangzi ~]$ impdp pangzi/pangzi dumpfile=syb.dmp directory=expdir remap_schema=syb:pangzi remap_tablespace=syb:pangzi
Import: Release 10.2.0.1.0 - Production on Saturday, 21 April, 2012 18:49:24
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "PANGZI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PANGZI"."SYS_IMPORT_FULL_01": pangzi/******** dumpfile=syb.dmp directory=expdir remap_schema=syb:pangzi remap_tablespace=syb:pangzi
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "PANGZI"."DYSPANGZI" 48.50 KB 659 rows
. . imported "PANGZI"."ALL_SALES" 13.68 KB 360 rows
中间部分导入信息省略
. . imported "PANGZI"."PURCHASES_WITH_TIMESTAMP" 5.609 KB 1 rows
. . imported "PANGZI"."REG_EXPS" 5.437 KB 1 rows
. . imported "PANGZI"."SALARY_GRADES" 5.710 KB 4 rows
. . imported "PANGZI"."PRODUCT_PRICE_AUDIT" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "PANGZI"."SYS_IMPORT_FULL_01" successfully completed at 18:49:37
成功导入！！！！
查看默认的表空间
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DYSPANGZI PANGZI
CUSTOMERS PANGZI
PRODUCT_TYPES PANGZI
PRODUCTS PANGZI
PURCHASES PANGZI
EMPLOYEES PANGZI
SALARY_GRADES PANGZI
PURCHASES_WITH_TIMESTAMP PANGZI
PURCHASES_TIMESTAMP_WITH_TZ PANGZI
PURCHASES_WITH_LOCAL_TZ PANGZI
COUPONS PANGZI
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PROMOTIONS PANGZI
ORDER_STATUS PANGZI
PRODUCT_CHANGES PANGZI
MORE_PRODUCTS PANGZI
MORE_EMPLOYEES PANGZI
DIVISIONS PANGZI
JOBS PANGZI
EMPLOYEES2 PANGZI
ALL_SALES PANGZI
PRODUCT_PRICE_AUDIT PANGZI
REG_EXPS PANGZI
已选择22行。
此表空间中没有索引，所以第一种方法也没有报错，如果出现有索引的第一种方法还得增加几步，等我明天找到周五那个数据再来再着做试验。
-----------------------------------------------------------
今天特意去公司找来了那天导的数据库，回来做实验。采用第一种方法不可行，虽然说可以使用IMP 加indexs参数来导出一些索引，但是导入的时候总有莫名其妙的错误，估计还是我没弄好，所以为了省事还是直接用第二种吧，方便，无错。