Introduction of new characteristics of Oracle 11g R2 data Pump (iv) Data pump import new Data_options

Oracle's 11GR2 version does not make much of a change to the data pump, mainly by adding support for the original version parameters and removing some small restrictions.

This article introduces the data pump to import the new data_options parameters.

Prior to 11.2, the data pump import provided only one data_options--skip_constraint_errors, and in 11.2, Data_options added an available value: Diable_append_hint.

In general, we want the data pump to be imported using a direct path, because the import is highly efficient. But sometimes, we do not need to adopt a direct path, because this way of the target table in the concurrent access has a great impact, and Diable_append_hint will be the right to control over to us.

Sql> CREATE TABLE T_append (ID number, name VARCHAR2 (30));

Table has been created.

sql> INSERT INTO T_append

2 Select RowNum, object_name

3 from All_objects;

55625 lines have been created.

Sql> commit;

Submit completed.

A test table was created, and the following data pump was used to export the table:

[Oracle@bjtest ~]$ EXPDP test/test DUMPFILE=T_APPEND.DP directory=d_output

Export:release11.2.0.1.0-production on Wednesday September 9 19:05:32 2009

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

Connect to: Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and real application testing options

Start "TEST". Sys_export_table_01 ": test/******** dumpfile=t_append.dp directory=d_output tables=t_append

Using the blocks method to estimate ...

Working with Object Types Table_export/table/table_data

Total estimate using the Blocks method: 3 MB

Working with Object Types table_export/table/table

. . "TEST" was exported. T_append "1.839 MB 55625 Lines

The primary table ' TEST ' was successfully loaded/unloaded. Sys_export_table_01 "

******************************************************************************

TEST. The set of dump files for sys_export_table_01 is:

/home/oracle/t_append.dp

The job "TEST". Sys_export_table_01 "completed successfully at 19:05:41

The following is the default import method:

[Oracle@bjtest ~]$ impdp test/test dumpfile=t_append.dp directory=d_output tables=t_append content=data_only

Import:release11.2.0.1.0-production on Wednesday September 9 19:27:49 2009

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

Connect to: Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and real application testing options

The primary table ' TEST ' was successfully loaded/unloaded. Sys_import_table_01 "

Start "TEST". Sys_import_table_01 ": test/******** dumpfile=t_append.dp directory=d_output tables=t_append content=data_only

Working with Object Types Table_export/table/table_data

. . The "TEST" was imported. T_append "1.839 MB 55625 Lines

The job "TEST". Sys_import_table_01 "completed successfully at 19:27:53

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

The default Append method takes only 4 seconds to load the data in. But this approach requires the table, and if someone else is working on it, it can cause a concurrency problem:

sql> Update t_append Set name = Lower (name) where id = 1;

2 rows have been updated.

An update operation was performed in one session to start another session to update a different record:

Sql> set SQLP ' sql2> '

sql2> Update t_append Set name = Lower (name) where id = 2;

2 rows have been updated.

You can see that two session updates can be performed as a result of updating different records, and the following rollback session 2:

sql2> rollback;

Fallback is complete.

Now that the update for session 1 still holds the lock, the following default import operation is performed:

[Oracle@bjtest ~]$ impdp test/test dumpfile=t_append.dp directory=d_output tables=t_append content=data_only

Import:release11.2.0.1.0-production on Wednesday September 9 20:20:45 2009

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