expdp /impdp 导数据的时候，临时表空间的使用

 

创建u1用户的临时表空间是tempdata ，数据库的默认临时表空间是temp，在scott下有一个表tm_out_b1 表，如果我们用scott用户导出tm_out_b1表，那么在导出过程中占用临时表空间是 scott用户的默认临时表空间temp

create temporary tablespace tempdata  tempfile 'D:\APP\ADMINISTRATOR\ORADATA\ORA11G\TEMPdata01.DBF' size 100M;

create user u1 identified by u1;

grant connect,resource to u1;

alter   user u1 temporary tablespace tempdata ;

SQL> select * from  database_properties  where property_name='DEFAULT_TEMP_TABLESPACE';
 
PROPERTY_NAME                  PROPERTY_VALUE                                                                   DESCRIPTION
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                                                                             Name of default temporary tablespace

 select   USERNAME, TEMPORARY_TABLESPACE from dba_users where username in ('U1','SCOTT');
 
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SCOTT                          TEMP
U1                             TEMPDATA

C:\Users\Administrator>expdp u1/u1 dumpfile=tmp.dmp directory=dir tables=scott.tm_out_b1

Export: Release 11.2.0.1.0 - Production on 星期一 5月 7 16:36:24 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "U1"."SYS_EXPORT_TABLE_02":  u1/******** dumpfile=tmp.dmp directory=dir tables=scott.tm_out_b
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 10.62 GB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 导出了 "SCOTT"."TM_OUT_B1":"T_OUT_B_2012_01"       9.182 GB 51565563 行
. . 导出了 "SCOTT"."TM_OUT_B1":"OTHER"                 2.514 MB   13297 行
. . 导出了 "SCOTT"."TM_OUT_B1":"T_OUT_B_2012_02"       141.7 KB     691 行
. . 导出了 "SCOTT"."TM_OUT_B1":"T_OUT_B_2012_04"       83.10 KB     399 行
. . 导出了 "SCOTT"."TM_OUT_B1":"T_OUT_B_2012_03"       16.54 KB      15 行
已成功加载/卸载了主表 "U1"."SYS_EXPORT_TABLE_02"
******************************************************************************
U1.SYS_EXPORT_TABLE_02 的转储文件集为:
  E:\DUMP\TMP.DMP
作业 "U1"."SYS_EXPORT_TABLE_02" 已于 16:39:59 成功完成

 

用u1用户导出，用的是u1的临时用户表空间，这样不会太影响系统临时表空间的使用

SQL> select USERNAME,TABLESPACE,BLOCKS from v$sort_usage;
 
USERNAME                       TABLESPACE                          BLOCKS
------------------------------ ------------------------------- ----------
SYS                            TEMP                                   128
SCOTT                          TEMP                                   128
U1                             TEMPDATA                               128
U1                             TEMPDATA                               128
U1                             TEMPDATA                               128
U1                             TEMPDATA                               128
U1                             TEMPDATA                               128
U1                             TEMPDATA                               128
 
8 rows selected

 

用scott用户导出或者导入会占用系统临时表空间的使用

SQL> select USERNAME,TABLESPACE,BLOCKS from v$sort_usage;
 
USERNAME                       TABLESPACE                          BLOCKS
------------------------------ ------------------------------- ----------
SYS                            TEMP                                   128
SCOTT                          TEMP                                   128
SCOTT                          TEMP                                   128
SCOTT                          TEMP                                   128
SCOTT                          TEMP                                   128
SCOTT                          TEMP                                   128
SCOTT                          TEMP                                   128
SCOTT                          TEMP                                   128
 
8 rows selected 

 

同样，导入过程也是一样

C:\Users\Administrator>impdp u1/u1 dumpfile=tmp_1.dmp directory=dir tables=scott.tm_out_b1 remap_sche
ma=scott:u1 logfile=1.log   TABLE_EXISTS_ACTION=replace

Import: Release 11.2.0.1.0 - Production on 星期一 5月 7 17:00:16 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "U1"."SYS_IMPORT_TABLE_01"
启动 "U1"."SYS_IMPORT_TABLE_01":  u1/******** dumpfile=tmp_1.dmp directory=dir tables=scott.tm_out_b1
 remap_schema=scott:u1 logfile=1.log TABLE_EXISTS_ACTION=replace
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: 表数据对象 "U1"."TM_OUT_B1":"T_OUT_B_2012_01" 无法加载/卸载并且被跳过, 错误如下:
ORA-02354: 导出/导入数据时出错
ORA-01688: 表 U1.TM_OUT_B1 分区 T_OUT_B_2012_01 无法通过 8192 (在表空间 USERS 中) 扩展
. . 导入了 "U1"."TM_OUT_B1":"OTHER"                    2.514 MB   13297 行
. . 导入了 "U1"."TM_OUT_B1":"T_OUT_B_2012_02"          141.7 KB     691 行
. . 导入了 "U1"."TM_OUT_B1":"T_OUT_B_2012_04"          83.10 KB     399 行
. . 导入了 "U1"."TM_OUT_B1":"T_OUT_B_2012_03"          16.54 KB      15 行
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "U1"."SYS_IMPORT_TABLE_01" 已经完成, 但是有 1 个错误 (于 17:03:14 完成)

 

SQL> select USERNAME,TABLESPACE,BLOCKS*8/1024 from v$sort_usage;
 
USERNAME                       TABLESPACE                      BLOCKS*8/1024
------------------------------ ------------------------------- -------------
SYS                            TEMP                                           1
U1                             TEMPDATA                                    1
U1                             TEMPDATA                                   1
U1                             TEMPDATA                                    1
U1                             TEMPDATA                                    1
U1                             TEMP                                            1

我们看到u1用户会使用很少的系统临时表空间