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

Source: Internet
Author: User
Tags reserved rollback

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.

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.