數據庫中數據泵避免個別表數據的導出方法是什么

本篇內容介紹了“數據庫中數據泵避免個別表數據的導出方法是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

對于數據泵EXPDP/IMPDP而言，功能比普通EXP/IMP功能要強的多，因此也可以實現一些普通導出導入工具很難完成的工作。

比如今天碰到的這個問題，要導出一些表，但是其中個別表只導出結構而不導出數據。

SQL> conn test/test
Connected.
SQL> set pages 100 lines 120
SQL> select count(*) from t;

  COUNT(*)
----------
        23

SQL> select count(*) from tt;

  COUNT(*)
----------
        72

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt1.dp tables=(t,tt)

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 25 8月, 2009 16:04:58

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=d_output dumpfile=t_tt1.dp tables=(t,tt)
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T"                                  5.953 KB      23 rows
. . exported "TEST"."TT"                                 6.421 KB      72 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /home/oracle/t_tt1.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:05:02

用T和TT表作為例子，分別代表需要導出結構的表和同時包含結構和數據的表。

這個需求對于普通的EXP/IMP來說，只能通過兩次導出操作來完成，一次導出包含數據的表，另一個通過執行ROWS=N導出僅需要結構的表。

對于EXPDP來說，同樣可以使用類似的方法，參數CONTENT控制導出的結構、數據還是全部：

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt2.dp tables=(t,tt) content=metadata_only

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 25 8月, 2009 16:32:59

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=d_output dumpfile=t_tt2.dp tables=(t,tt) content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /home/oracle/t_tt2.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:33:02

但是這種方法控制的是整體，現在需要對其中的個別對象只導出表結構。最好想到的方法是通過QUERY來控制：

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt3.dp tables=(t,tt) query='t:"where 1=2"'

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 25 8月, 2009 16:51:37

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=d_output dumpfile=t_tt3.dp tables=(t,tt) query=t:"where 1=2"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T"                                  5.507 KB       0 rows
. . exported "TEST"."TT"                                 6.421 KB      72 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /home/oracle/t_tt.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:51:41

通過對T表添加一個恒為FALSE的查詢條件，使得T表導出的時候獲取0條記錄，從而達到只導T的結構的目的。

但是這種方法對于數據量比較大的表效率會比較低，因為Oracle會進行導出的操作，只是在處理的時候將記錄過濾掉，除了沒有將數據寫到導出文件，其他所有的操作都進行了，因此效率很低。

而實際上，數據泵還有更好的辦法來解決這個問題：使用EXCLUDE參數。

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_tt4.dp tables=(t,tt) exclude=table/table_data:\"=\'T\'\"

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 25 8月, 2009 16:59:39

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=d_output dumpfile=t_tt4.dp tables=(t,tt) exclude=table/table_data:"='T'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."TT"                                 6.421 KB      72 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /home/oracle/t_tt4.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:43

這里看不到T表的信息，下面檢查一下導出是否生效：

[oracle@yans1 ~]$ sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 8月 25 17:00:27 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(7)

SQL> drop table t;

Table dropped.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t_tt4.dp tables=t

Import: Release 10.2.0.3.0 - 64bit Production on 星期二, 25 8月, 2009 17:00:41

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/******** directory=d_output dumpfile=t_tt4.dp tables=t
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 17:00:43

[oracle@yans1 ~]$ sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 8月 25 17:00:47 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(7)

SQL> select * from t;

no rows selected

很顯然，利用EXCLUDE的方式使得數據泵導出的時候去掉了T表的數據。

“數據庫中數據泵避免個別表數據的導出方法是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！