Oralce data Pump How to avoid the export of individual table data

For data pump EXPDP/IMPDP, the function is much stronger than the ordinary exp/imp function, so it is difficult to accomplish some common export import tools.

For example, the problem that comes up today is to export some tables, but the individual tables only export the structure without exporting the data.

Sql> Conn Test/test

Connected.

Sql> Set Pages Lines 120

Sql> Select COUNT (*) from T;

COUNT (*)

----------

23

Sql> Select COUNT (*) from TT;

COUNT (*)

----------

72

Sql> exit

Disconnected from Oracle database10genterprise Edition release10.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:release10.2.0.3.0-64bit Production on Tuesday, 25 August, 2009 16:04:58

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

Connected to:oracle database10genterprise Edition release10.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 rows

. . Exported "TEST". " TT "6.421 MB 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

Use the T and TT tables as examples, representing the tables that need to be exported and the tables that contain both the structure and the data.

This requirement can only be done through two export operations for ordinary exp/imp, one to export the table containing the data, and the other to export only the structure-only tables by performing rows=n.

For EXPDP, a similar approach can be used, with parameter content controlling the structure, data, or all of the export:

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

Export:release10.2.0.3.0-64bit Production on Tuesday, 25 August, 2009 16:32:59

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

Connected to:oracle database10genterprise Edition release10.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

But this method controls the whole and now needs to export only the table structure to the individual objects. The best way to think about this is by using query to control:

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

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45463.htm