大家都知道在数据泵中有一个参数 tablespaces，也就是 expdp可以导出表空间，那么就是导出整个表空间数据吗？

下面我们来测试一下：

创建一个表空间，此处省略。我创建几张表，并且创建一个分区表
，语句如下：分区使用了两个表空间，TEST 和USERS

点击( 此处)折叠或打开

CREATE TABLE scott .obj_part
PARTITION BY RANGE (created )
(
PARTITION p20101231 VALUES LESS THAN (to_date ( '20101231' , 'yyyymmdd' ) ) tablespace test ,
PARTITION p20111231 VALUES LESS THAN (to_date ( '20111231' , 'yyyymmdd' ) ) tablespace test ,
PARTITION p20121231 VALUES LESS THAN (to_date ( '20121231' , 'yyyymmdd' ) ) tablespace users ,
PARTITION p20131231 VALUES LESS THAN (to_date ( '20131231' , 'yyyymmdd' ) ) tablespace users ,
PARTITION p20141231 VALUES LESS THAN (to_date ( '20141231' , 'yyyymmdd' ) ) tablespace users ,
PARTITION p20151231 VALUES LESS THAN (to_date ( '20151231' , 'yyyymmdd' ) ) tablespace users ,
PARTITION p20161231 VALUES LESS THAN (to_date ( '20161231' , 'yyyymmdd' ) ) tablespace users ,
PARTITION p20171231 VALUES LESS THAN (to_date ( '20171231' , 'yyyymmdd' ) ) tablespace users
)
as select * from dba_objects where 1 =0

插入数据：

点击( 此处)折叠或打开

SQL > insert into scott .obj_part select * from dba_objects ;
86383 rows created .
SQL > commit ;
Commit complete .
SQL > conn test / test
Connected .
SQL > insert into scott .obj_part select OWNER ,OBJECT_NAME ,SUBOBJECT_NAME ,OBJECT_ID ,DATA_OBJECT_ID ,OBJECT_TYPE ,to_date ( '2017-5-12' , 'yyyy-mm-dd' ) ,LAST_DDL_TIME ,TIMESTAMP ,STATUS ,TEMPORARY ,GENERATED ,SECONDARY ,NAMESPACE ,EDITION_NAME from dba_objects ;
86383 rows created .
SQL > commit ;
Commit complete .
SQL > create index scott .objpart_id on scott .obj_part (object_id ) tablespace test ;
Index created .
SQL > commit ;
Commit complete .

执行导出

点击( 此处)折叠或打开

[oracle@mystandby dump ] $ expdp \ ' / as sysdba\' directory =sh_dmp dumpfile =exp_tablespace01 .dmp logfile =exptablesp01 .log tablespaces = test
Export : Release 11 .2 .0 .4 .0 - Production on Thu Aug 10 20 :40 :29 2017
Copyright (c ) 1982 , 2011 , Oracle and / or its affiliates . All rights reserved .
Connected to : Oracle Database 11g Enterprise Edition Release 11 .2 .0 .4 .0 - 64bit Production
With the Partitioning , OLAP , Data Mining and Real Application Testing options
Starting "SYS" . "SYS_EXPORT_TABLESPACE_01" : "/******** AS SYSDBA" directory =sh_dmp dumpfile =exp_tablespace01 .dmp logfile =exptablesp01 .log tablespaces = test
Estimate in progress using BLOCKS method . . .
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method : 36 .12 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT" . "OBJ_PART" : "P20131231" 8 .358 MB 86200 rows
. . exported "SCOTT" . "OBJ_PART" : "P20171231" 8 .394 MB 86566 rows -- 注意此表表空间为USERS表空间
. . exported "TEST" . "TEST" 8 .435 MB 87003 rows
. . exported "TEST" . "T_C" 3 .035 MB 84748 rows
. . exported "TEST" . "T_ADV" 873 .0 KB 100000 rows
. . exported "TEST" . "T_P" 83 .70 KB 2870 rows
. . exported "SCOTT" . "OBJ_PART" : "P20101231" 0 KB 0 rows
. . exported "SCOTT" . "OBJ_PART" : "P20111231" 0 KB 0 rows
. . exported "SCOTT" . "OBJ_PART" : "P20121231" 0 KB 0 rows
. . exported "SCOTT" . "OBJ_PART" : "P20141231" 0 KB 0 rows
. . exported "SCOTT" . "OBJ_PART" : "P20151231" 0 KB 0 rows
. . exported "SCOTT" . "OBJ_PART" : "P20161231" 0 KB 0 rows
Master table "SYS" . "SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Dump file set for SYS .SYS_EXPORT_TABLESPACE_01 is :
/backup/dump/exp_tablespace01 .dmp
Job "SYS" . "SYS_EXPORT_TABLESPACE_01" successfully completed at Thu Aug 10 20 :40 :42 2017 elapsed 0 00 :00 :12

也就是使用expdp导出表空间时，并不是只导出该表空间所有数据，也会将相关联的相关对象数据导出。

下面贴出官方解释：

点击( 此处)折叠或打开

TABLESPACES
Default : There is no default
Purpose
Specifies a list of tablespace names to be exported in tablespace mode .
Syntax and Description
TABLESPACES =tablespace_name [ , . . . ]
In tablespace mode , only the tables contained in a specified set of tablespaces are unloaded . If a table is unloaded , then its dependent objects are also unloaded . Both object metadata and data are unloaded . If any part of a table resides in the specified set , then that table and all of its dependent objects are exported . Privileged users get all tables . Unprivileged users get only the tables in their own schemas

---大体意思
<1span style="color:#555555;font-family:" font-size:13px;white-space:normal;background-color:#ffffff;"="">在表空间模式中, 只导出指定表空间中所包含的表。如果导出了表, 则它的从属对象也将被导出。对象元数据和数据都将被导出。如果表的任何部分保存在在指定的表空间中, 则导出该表及其所有从属对象。特权用户获取所有表，没有特权的用户只能在自己的权限内获取表。

Filtering can restrict what is exported using this mode (see "Filtering During Export Operations" ) .
Restrictions
The length of the tablespace name list specified for the TABLESPACES parameter is limited to a maximum of 4 MB , unless you are using the NETWORK_LINK to an Oracle Database release 10 .2 .0 .3 or earlier or to a read -only database . In such cases , the limit is 4 KB .
Example
The following is an example of using the TABLESPACES parameter . The example assumes that tablespaces tbs_4 , tbs_5 , and tbs_6 already exist .
> expdp hr DIRECTORY =dpump_dir1 DUMPFILE =tbs .dmp
TABLESPACES =tbs_4 , tbs_5 , tbs_6
This results in a tablespace export in which tables ( and their dependent objects ) from the specified tablespaces (tbs_4 , tbs_5 , and tbs_6 ) will be unloaded .

使用任何命令、参数应该阅读下官方文档。

ITPub博客

【expdp】 expdp导出表空间，会导出什么

最新文章