Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what is the method of data pump to avoid the export of individual table data in the database". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

For the data pump EXPDP/IMPDP, the function is much stronger than the ordinary EXP/IMP, so it can also achieve some work that is difficult to be done by ordinary export and import tools.

For example, in the problem we encountered today, we need to export some tables, but individual tables export only the structure, not the data.

SQL > conn test/test

Connected.

SQL > set pages 100 lines 120

SQL > select count (*) from t

COUNT (*)

-

twenty-three

SQL > select count (*) from tt

COUNT (*)

-

seventy-two

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= (TJT)

Export: Release 10.2.0.3.0-64bit Production on Tuesday, August, 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= (tQuery 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

Use the T and TT tables as examples to represent tables that need to export structures and tables that contain both structure and data, respectively.

For a normal EXP/IMP, this requirement can only be accomplished through two export operations, one for a table that contains data, and the other for a table that requires only structure by performing ROWS=N.

For EXPDP, you can also use a similar method, where the parameter CONTENT controls whether the exported structure, data, or all:

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

Export: Release 10.2.0.3.0-64bit Production on Tuesday, August, 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= (tjingtt) 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 approach controls the whole, and now you need to export only the table structure for individual objects. The best way to think of is to control through QUERY:

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

Export: Release 10.2.0.3.0-64bit Production on Tuesday, August, 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= (tjingtt) 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

By adding a query condition which is always FALSE to the T table, the T table can get 0 records when it is exported, so as to achieve the purpose of guiding the structure of T only.

However, this method is less efficient for tables with a large amount of data, because Oracle will export, but filter out the records during processing, and all operations are carried out except that the data is not written to the export file, so the efficiency is very low.

In fact, the data pump has a better way to solve this problem: use the EXCLUDE parameter.

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

Export: Release 10.2.0.3.0-64bit Production on Tuesday, August, 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= (tQuery 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

You can't see the information about the T table here. Let's check whether the export is valid:

[oracle@yans1 ~] $sqlplus test/test

SQL*Plus: Release 10.2.0.3.0-Production on Tuesday August 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 Tuesday, August, 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 Tuesday August 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

Obviously, the data of T table is removed when the data pump is exported by the way of EXCLUDE.

This is the end of the content of "data pump in the database to avoid the export of individual table data". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.