宜宾网站建设公司

expdp如何导出表空间

这篇文章给大家分享的是有关expdp如何导出表空间的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

在大同等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供成都网站设计、网站建设 网站设计制作按需求定制开发,公司网站建设,企业网站建设,品牌网站设计,成都营销网站建设,成都外贸网站建设公司,大同网站建设费用合理。



下面我们来测试一下：

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

点击(此处)折叠或打开

  1. CREATE TABLE scott.obj_part

  2. PARTITION BY RANGE (created)

  3. (

  4.  PARTITION p20101231 VALUES LESS THAN (to_date('20101231','yyyymmdd')) tablespace test,

  5.  PARTITION p20111231 VALUES LESS THAN (to_date('20111231','yyyymmdd')) tablespace test,

  6.  PARTITION p20121231 VALUES LESS THAN (to_date('20121231','yyyymmdd')) tablespace users,

  7.  PARTITION p20131231 VALUES LESS THAN (to_date('20131231','yyyymmdd')) tablespace users,

  8.  PARTITION p20141231 VALUES LESS THAN (to_date('20141231','yyyymmdd')) tablespace users,

  9.  PARTITION p20151231 VALUES LESS THAN (to_date('20151231','yyyymmdd')) tablespace users,

  10.  PARTITION p20161231 VALUES LESS THAN (to_date('20161231','yyyymmdd')) tablespace users,

  11.  PARTITION p20171231 VALUES LESS THAN (to_date('20171231','yyyymmdd')) tablespace users

  12. )

  13. as select * from dba_objects where 1=0


插入数据：

点击(此处)折叠或打开

  1. SQL> insert into scott.obj_part select * from dba_objects;

  3. 86383 rows created.

  5. SQL> commit;

  7. Commit complete.

  9. SQL> conn test/test

  10. Connected.

  11. 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;

  13. 86383 rows created.

  15. SQL> commit;

  17. Commit complete.

  19. SQL> create index scott.objpart_id on scott.obj_part(object_id) tablespace test;

  21. Index created.

  23. SQL> commit;

  25. Commit complete.


执行导出

点击(此处)折叠或打开

  1. [oracle@mystandby dump]$ expdp \' / as sysdba\' directory=sh_dmp dumpfile=exp_tablespace01.dmp logfile=exptablesp01.log tablespaces=test

  3. Export: Release 11.2.0.4.0 - Production on Thu Aug 10 20:40:29 2017

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

  7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

  8. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  9. Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=sh_dmp dumpfile=exp_tablespace01.dmp logfile=exptablesp01.log tablespaces=test

  10. Estimate in progress using BLOCKS method...

  11. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

  12. Total estimation using BLOCKS method: 36.12 MB

  13. Processing object type TABLE_EXPORT/TABLE/TABLE

  14. Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

  15. Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

  16. Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

  17. Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

  18. Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

  19. . . exported "SCOTT"."OBJ_PART":"P20131231" 8.358 MB   86200 rows

  20. . . exported "SCOTT"."OBJ_PART":"P20171231" 8.394 MB   86566 rows   --注意此表表空间为USERS表空间

  21. . . exported "TEST"."TEST" 8.435 MB   87003 rows

  22. . . exported "TEST"."T_C" 3.035 MB   84748 rows

  23. . . exported "TEST"."T_ADV" 873.0 KB  100000 rows

  24. . . exported "TEST"."T_P" 83.70 KB    2870 rows

  25. . . exported "SCOTT"."OBJ_PART":"P20101231" 0 KB       0 rows

  26. . . exported "SCOTT"."OBJ_PART":"P20111231" 0 KB       0 rows

  27. . . exported "SCOTT"."OBJ_PART":"P20121231" 0 KB       0 rows

  28. . . exported "SCOTT"."OBJ_PART":"P20141231" 0 KB       0 rows

  29. . . exported "SCOTT"."OBJ_PART":"P20151231" 0 KB       0 rows

  30. . . exported "SCOTT"."OBJ_PART":"P20161231" 0 KB       0 rows

  31. Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

  32. ******************************************************************************

  33. Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:

  34.   /backup/dump/exp_tablespace01.dmp

  35. Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Thu Aug 10 20:40:42 2017 elapsed 0 00:00:12


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

下面贴出官方解释：

点击(此处)折叠或打开

  1. TABLESPACES

  2. Default: There is no default

  4. Purpose

  6. Specifies a list of tablespace names to be exported in tablespace mode.

  8. Syntax and Description

  10. TABLESPACES=tablespace_name [, ...]

  11. 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



---大体意思
在表空间模式中, 只导出指定表空间中所包含的表。如果导出了表, 则它的从属对象也将被导出。对象元数据和数据都将被导出。如果表的任何部分保存在在指定的表空间中, 则导出该表及其所有从属对象。特权用户获取所有表，没有特权的用户只能在自己的权限内获取表。

  2. Filtering can restrict what is exported using this mode (see "Filtering During Export Operations").

  4. Restrictions

  6. 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.

  8. Example

  10. The following is an example of using the TABLESPACES parameter. The example assumes that tablespaces tbs_4, tbs_5, and tbs_6 already exist.

  12. > expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp

  13. TABLESPACES=tbs_4, tbs_5, tbs_6

  14. 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.

感谢各位的阅读！关于“expdp如何导出表空间”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！


本文标题：expdp如何导出表空间
转载来于：http://ybzwz.com/article/jijgsg.html

其他资讯