expdp如何導出表空間

143次閱讀

沒有評論

共計 4673 個字符，預計需要花費 12 分鐘才能閱讀完成。

這篇文章給大家分享的是有關 expdp 如何導出表空間的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。

下面我們來測試一下：

創建一個表空間，此處省略。我創建幾張表，并且創建一個分區表
，語句如下：分區使用了兩個表空間，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

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

— 大體意思
在表空間模式中, 只導出指定表空間中所包含的表。如果導出了表, 則它的從屬對象也將被導出。對象元數據和數據都將被導出。如果表的任何部分保存在在指定的表空間中, 則導出該表及其所有從屬對象。特權用戶獲取所有表，沒有特權的用戶只能在自己的權限內獲取表。

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.

感謝各位的閱讀！關于“expdp 如何導出表空間”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

正文完