Oracle 10g/11g Import/Export

Basic Import and Export commands for Oracle10g/11g:

 
ExpUser Name/User Password@ Service name File=Save path. dmp Buffer=8192Filesize=10000 m feedback=1000 imp username/user password @ service name file = file path. dmp full = y
 

Generally, data export (including triggers and stored procedures) under a specified user can be completely exported (imported) within 10 Gb ). In addition, you can also export the specified table and specify functions. For more information, see the official Oracle documentation (after installation, the EM interface appears to be available and can be found on the official website ). In general, 10 Gb of data can be imported and exported conveniently.

Note: 1. If the exp/IMP command can be used to connect to the database, it can be used on the server (remote.

2, oracle11g basic Import and Export command is the same as Oracle10g, it should be noted that the 11g export has a time-space table will not export, the problem refer to the http://www.cnblogs.com/GYoungBean/archive/2012/07/24/2605963.html

3. When importing data of different versions, the later version can be compatible with the exported files of the lower version. However, pay attention to the tablespace name, index, version number, and other issues.

----------------------------------- Split line -----------------------------------------------------------

Use expdp/impdp to export/import files:

When using the expdp command, you must specify the folder to be dumped.

1. Basic export method:

 Create   Or   Replace Directory test_dir (directory alias) As   '  /U01/APP/Oracle/oradata/  '  (The path on the hard disk );  Grant   Read , WriteOn Directory test_dir (directory alias) To Scott (User Name specified) /  Public  (ALL users); expdp Scott  /Scott @ orcl Directory = Test_dir dumpfile = XX. dmp Schemas = Scott (username) logfile = expdpscott. Log version =  10.2 . 0.1 .0 (imported data version number, which is an optional parameter in the same version) 

2. Parallel export:

 
Expdp Scott/Scott@ OrclDirectory=Test_dir parallel = 4 dumpfile=XX _ % u. dmp Schemas=Scott (User Name) logfile=Expdpscott.LogVersion=10.2.0.1.0
 

Parallel is the parallel export parameter. You can specify the number of threads used for export. _ % U is a wildcard. The exported format is xx01.dmp and xx02.dmp.

Note: 1. expdp/impdp is a server-side command. Therefore, you can only use expdp/impdp on the server at this time. (If you export data remotely, go to the following link)

2. Make sure that the folder exists in the hard disk and the path is correct before you exit.

3. When using version, if the data imported from 11 GB is to be imported to 10 Gb, specify the version number of 10 Gb.

4. For the import command, see the document (see section B)

----------------------------------- Split line -----------------------------------------------------------

Remotely export data using the expdp command:

When using remote export, make sure that the remote computer has an Oracle client installed and can be connected to the server. At the same time, a dump folder must be created and the directory must be created locally.

 
Create DatabaseLink remote_scott (dblink alias) connectToScott (user name to be connected) identifiedByTiger (User Password to be connected) using'Dev'(Remote database service name);
 

 
Expdp test/test @ db10g (Local Database User) tables = Scott. EMP (remote) network_link = remote_scott (dblink name) Directory = test_dir dumpfile = EMP. dmp logfile = exp. Log
 

The idea is to establish db_link between the local database and the remote database. In the preceding statement, tables = Scott. EMP (remote) is only a table (all tables under the user are exported ).

Note: 1. It is not recommended to import data in the remote end. It is slow when the data volume is large, and a client is required.

2. If db_link is not used, see references.

----------------------------------- Split line -----------------------------------------------------------

References:

A. http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_5007.htm

B. http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php#GettingStarted

 

---------------------------------- Split line --------------------------------------------------------------

Addendum

1. When using expdp to export data, it sometimes gets stuck at Statistics (what is missing ). In this case, use the exclude = statistics parameter during export.

2. During import, you also need to add the parameter exclude = statistics.

 

From: http://www.cnblogs.com/GYoungBean/archive/2012/09/17/2688414.html