Oracle數據泵content=data_only

Oracle數據泵，邏輯備份和恢復工具，他直接在邏輯從面操作，不能想象成單純的insert ， 而且只導出數據，然后恢復數據時候，可以

下面看一個試驗 ,驗證的是只導出數據后，，可以恢復，就算是表結構已經變化了，他也能把相應的列恢復，

1)SQL> desc liuwenhe.liuwenhe;

Name Null? Type

----------------------------------------- -------- ----------------------------

X NOT NULL NUMBER(38)

Y NUMBER(38)
2)SQL> select * from liuwenhe.liuwenhe;

X Y

---------- ----------

1
3
3
3
2
4
4
5
5
8
6
9

6 rows selected.
3)[Oracle@rac1 expdp]$ expdp system/manager123 directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe;

Export: Release 11.2.0.3.0 - Production on Mon Jul 6 11:52:56 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_03": system/******** directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
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/STATISTICS/TABLE_STATISTICS
. . exported "LIUWENHE"."LIUWENHE" 5.492 KB 6 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:
/backup/expdp/hhhf.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_03" successfully completed at 11:53:10
4)SQL> alter table liuwenhe.liuwenhe drop column y;

Table altered.
5)SQL> truncate table liuwenhe.liuwenhe;

Table truncated.
6)[oracle@rac1 expdp]$ impdp system/manager123 directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe content=data_only;

Import: Release 11.2.0.3.0 - Production on Mon Jul 6 11:55:07 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TABLE_04" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_04": system/******** directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe content=data_only

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "LIUWENHE"."LIUWENHE" 5.492 KB 6 rows

Job "SYSTEM"."SYS_IMPORT_TABLE_04" successfully completed at 11:55:13

7)SQL> select * from liuwenhe.liuwenhe;

X

----------

1

2

3

4

5

6

6 rows selected.

總結：Oracle數據泵，邏輯備份和恢復工具，他直接在邏輯從面操作，不能想象成單純的insert ， 而且只導出數據，然后恢復數據時候，可以不用寫remap_tablespace這個參數 ，tables=liuwenhe.liuwenhe 這里要是沒有寫前綴liuwenhe,而寫成tables=liuwenhe,系統就認為是system用戶下的liuwenhe表，而出錯。

本文永久更新鏈接地址：

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com