Example of Oracle client using expdp/impdp

 

1. Instructions on using expdp/impdp on the client

 

Some documents about Oracle expdp/impdp commands have been prepared. The links are as follows:

Comparison between exp/IMP and expdp/impdp and some optimization items in use

Http://blog.csdn.net/tianlesoftware/article/details/6093973

 

Oracle 10g Data Pump expdp/impdp

Http://blog.csdn.net/tianlesoftware/article/details/4674224

 

Oracle expdp/impdp example

Http://blog.csdn.net/tianlesoftware/article/details/6260138

 

Example of Oracle expdp/impdp from high version to low version

Http://blog.csdn.net/tianlesoftware/article/details/6533421

 

This article mainly introduces the following points: You can use the expdp/impdp command on the client. Many articles on the Internet say that expdp/impdp is a server-side command and can only be used on the server. In fact, there are some problems with this Statement. Let's take a look at a text description on the official website:

 

Http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL801

 

Oracle Data Pump is made up of three distinct parts:

(1) The command-lineclients, expdp and impdp

(2) The DBMS_DATAPUMP PL/sqlpackage (also known as the Data Pump API)

(3) the DBMS_METADATA PL/sqlpackage (also known as the Metadata API)

 

The data pumpclients, expdp and impdp, invoke the data pump export utilityand Data Pump import utility, respectively.

-- The Data Pump client (exdp/impdp) calls the data pump export/import tool.

The expdp and impdp clientsuse the procedures provided in the DBMS_DATAPUMP PL/SQL package toexecute Export and Import commands, using the parameters entered at the CommandLine. these parameters enable the exporting and importing of data and metadatafor a complete database or for subsets of a database.

-- The expdp/impdp client uses the DBMS_DATAPUMP package to export and import data.

When metadata ismoved, data pump uses functionality provided bydbms_metadata PL/SQL package. The DBMS_METADATA packageprovides a centralized facility for the extraction, manipulation, Andre-creation of dictionary metadata.

The DBMS_DATAPUMP and DBMS_METADATA PL/sqlpackages can be used independently of the Data Pump clients.

 

All data pump export and import processing, including the reading and writing of dump files, is done on the system (server) selected by the specified database connect string.

-- All dump files are stored in the directory specified on the server.

This means thatfor unprivileged users, the database administrator (DBA) must create directoryobjects for the Data Pump files that are read and written on that server filesystem.

 

The preceding text is summarized as follows:

The DataPump Client Command (expdp/impdp) will call the DBMS_DATAPUMP PL/sqlpackage and DBMS_METADATA PL/SQL packages. These two packages are on the server and we will execute expdp/impdp on the client. However, the generated dump file still exists in the directory specified on the server.

Therefore, it is not a legend on the Internet that expdp/impdp can only be used on the server.

 

 

Ii. Example of using expdp/impdp on the client

 

Server version:

SQL> select * from V $ version;

 

Banner

--------------------------------------------------------------------------------

Oracle Database 11g enterprise editionrelease 11.2.0.3.0-64bit Production

PL/SQL release 11.2.0.3.0-Production

Core 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

Nlsrtl version 11.2.0.3.0-Production

 

Create directory on the server:

SQL> create directory backup as '/u01/backup ';

Directory created.

 

SQL> grant read, write on directory backupto Dave;

Grant succeeded.

 

Configure tnsnames. ora in the client as follows:

DG =

(Description =

(Address_list =

(Address = (Protocol = TCP) (host = 192.168.3.11) (Port = 1521 ))

)

(CONNECT_DATA =

(SID = DG)

(Server = dedicated)

)

)

 

Run the expdp command on the Windows platform where the server version is installed:

C: \ Users \ administrator. David Dai> expdp Dave/dave @ DG directory = backup dumpfile = Dave. dmp logfile = Dave. Log schemas = Dave

 

Export: release11.2.0.1.0-production on Thu Dec 15 16:41:56 2011

-- Note that the 11g expdp is used.

 

Copyright (c) 1982,200 9, Oracle and/or itsaffiliates. All rights reserved.

 

Connected to: Oracle Database 11 genterprise Edition Release 11.2.0.3.0-64bit Production

With the partitioning, OLAP, data miningand real application testing options

Starting "Dave". "sys_export_schema_01": Dave/******** @ DG directory = backupdumpfile = Dave. dmp logfile = Dave. Log schemas = Dave

Estimate in progress using blocks method...

Processing object typeschema_export/table/table_data

Total estimation using blocks method: 64 KB

Processing object typeschema_export/pre_schema/procact_schema

Processing object typeschema_export/table

Processing object typeschema_export/table/comment

Processing object typeschema_export/table/index/Index

Processing object typeschema_export/table/constraint/Constraint

Processing object typeschema_export/table/index/statistics/index_statistics

.. Exported "Dave". "Dave" 6.539 kb 31 rows

Master table "Dave". "sys_export_schema_01" successfully Loaded/unloaded

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

Dump File set for Dave. sys_export_schema_01is:

/U01/backup/Dave. dmp

Job "Dave". "sys_export_schema_01" successfully completed at16: 44: 11

 

Exported successfully. Let's check the/u01/backup directory:

[Root @ DG1 u01] # cd/u01/backup/

[Root @ DG1 backup] # ll

Total 168

-RW-r ----- 1 Oracle oinstall 163840 dec 1516: 44 Dave. dmp

-RW-r -- 1 Oracle oinstall 1322 Dec 15 Dave. Log

 

-- Clear the Directory and prepare for the next expdp:

[Root @ DG1 backup] # rm-RF *

 

 

Switch to the machine where only the Oracle 10g client is installed and execute expdp:

C: \ Users \ Administrator> expdp Dave/dave @ DG directory = backup dumpfile = Dave. dmp logfile = Dave. Log schemas = Dave

Export: release10.2.0.3.0-production on Thursday, 15 Aug 17, 2011 16:56:09

-- Note the expdp version:

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connect to: oracledatabase 11g Enterprise Edition Release 11.2.0.3.0-64bit produc

Tion

With the partitioning, OLAP, data miningand real application testing options

Start "Dave". "sys_export_schema_01": Dave/******** @ DG directory = backup dumpfile =

Dave. dmp logfile = Dave. Log schemas = Dave

Using the blocks Method for estimation...

Processing object type schema_export/table/table_data

Total estimation using the blocks method: 64 KB

Processing object type schema_export/pre_schema/procact_schema

Processing object type schema_export/table

Processing object type schema_export/table/comment

Processing object type schema_export/table/index/Index

Processing object type schema_export/table/constraint/Constraint

Processing object type schema_export/table/index/statistics/index_statistics

.. Exported "Dave". "Dave" 6.539 kb 31 lines

The master table "Dave". "sys_export_schema_01" is successfully loaded/uninstalled"

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

The dump file set of Dave. sys_export_schema_01 is:

/U01/backup/Dave. dmp

The job "Dave". "sys_export_schema_01" was successfully completed at 16:57:58.

 

View the backup file:

[Root @ DG1 backup] # ll-H

Total 168 K

-RW-r ----- 1 Oracle oinstall 160 K dec 1516: 57 Dave. dmp

-RW-r -- 1 Oracle oinstall 1.2 K dec 1516: 57 Dave. Log

 

 

So far, we have a 10g expdp to export the 11g oracledump file. Now we use impdp of 11g to import the dump:

 

C: \ Users \ administrator. David Dai> impdp Dave/dave @ DG directory = backup dumpfile = Dave. dmp logfile = imp. Log schemas = davetable_exists_action = replace

 

Import: Release 11.2.0.1.0-production on Thu Dec 15 17:28:43 2011

 

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

 

Connected to: Oracle Database 11g Enterprise Edition release11.2.0.3.0-64bit Production

With the partitioning, OLAP, data mining and real applicationtesting options

Master table "Dave". "sys_import_schema_01" successfully Loaded/unloaded

Starting "Dave". "sys_import_schema_01": Dave/******** @ DG directory = backupdumpfile = Dave. dmp logfile = imp. Log schemas = Dave table_exists_action = replace

Processing object type schema_export/pre_schema/procact_schema

Processing object type schema_export/table

Processing object type schema_export/table/table_data

.. Imported "Dave". "Dave" 6.539 kb 31 rows

Job "Dave". "sys_import_schema_01" successfullycompleted at 17:29:03

 

Import successful. This indicates that the compatibility of expdp from low to high is no problem. If it is from high to low, the version parameter needs to be executed during expdp. This is described in the previous link.

 

Summary:

Expdp/impdp can be used on the client. You can use @ to specify the service name.

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Group dba6: 158654907 dba7: 172855474