Oracle10gexpdp/impdp practice summary in the project

Since Oracle10g version, Data Pump (datadump) is provided as a new data mobile technology. The technical tools are expdp and impdp respectively. We

Since Oracle version 10g, data Pump is provided as a new data mobile technology. The technical tools are expdp and impdp respectively. We

Since Oracle version 10g, data Pump is provided as a new data mobile technology. The technical tools are expdp and impdp respectively. We often use them in actual projects, such as migrating data from the production database to the test database.

I will introduce the usage, usage, and differences between these two tools and the imp/exp of 9i.

Expdp and impdp have three main purposes:

1. implement logical backup and recovery of databases;

2. Move database objects such as table definitions and data, functions, and stored procedures between different users of the same or different databases;

3. It can be used together with rman to achieve rapid data migration at the tablespace level.

(Note: I specifically noted the implementation process in this article: exp/expdp transfers tablespaces and rman convert for fast migration of large data volumes.

)

Expdp and impdp have many Parameter options, and different options implement different functions. Here we will focus on the main functions of expdp.

1. Export a user and add schemas

2. Export some tables under a user and add tables

3. Export tables not included in a user, and add exclude

4. Export the records of all tables that meet a certain condition (such as the first 10000 rows of the table) and add query

5. Export the data image of a certain time point or SCN, and add flashback_time or flashback_scn

This function is used to instantiate Data Synchronization tools such as streams and ogg.

6. Export the metadata or ALL data of the database, and add CONTENT = {ALL | DATA_ONLY | METADATA_ONLY}

7. Export the metadata of a tablespace in the database for rapid table space migration, and add transport_tablespace = y tablespaces = tbs_name

8. restrict the size of each exported file and add filesize

You can also limit the size of each exported file, add the degree of parallelism, and count export objects and other functions. These functions are not commonly used daily. You can use the expdp-help parameter.

An example of using expdp is as follows:

Expdp qlzq/qlzqp SCHEMAS = ebmall directory = test dumpfile = ebmall. dmp logfile = ebmall. log

This is an operation to export all objects under the user's ebmall to the ebmall. dmp file under the test directory. The recorded log file is ebmall. log.

Expdp qlzq/qlzqp directory = test dumpfile = qlzq. dmp logfile = qlzq. log EXCLUDE = TABLE: \ "IN \ (\ 'cms _ DOC_BODY \ ', \ 'mail_queue_file \', \'s

ERVICE_LOG \ ', \ 'msg _ MAIL_HISTORY \', \ 'mail _ QUEUE_FILE \'\)\"

This operation is used to export all objects except some tables under a user. During the use of expdp, these three parameters directory, dumpfile, and logfile must be added.

The impdp tool has many functions. The main function used is rmap_schema. The parameter is REMAP_SCHEMA = source_schema: target_schema.

For other functions, see impdp-help.

Impdp system/CMDL directory = qlzq dumpfile = qlzq. dmp logfile = qlzq. log

On the target database, there is no qlzq user before the import. It can be created automatically when impdp is used for import. The passwords of new users are the same as those of the source database. Before importing data, you must set up the tablespace with proper size.

If the dmp file of the source database is another user exported by the system user, add the schemas option to specify the user to import.

If the user exporting the dmp file is qlzq, We need to execute the schemals parameter value during import.

The usage is as follows:

Impdp system/CMDL directory = qlzq schemas = ebmall dumpfile = ebmall. dmp logfile = ebmall. log

I think the main difference between 10g expdp/impdp AND exp/imp is that the former can only run on the database server, and the latter can run simultaneously on the client or server.

This difference involves cross-platform data import and export.

For more information about Oracle, see the Oracle topic page? Tid = 12