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

Source: Internet
Author: User
Tags reserved

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.