Use of exp/imp Export Import Tool

Use of exp/imp Export Import Tool

1. Export tool exp

1. It is the directory for storing the next executable file in the operating system/ORACLE_HOME/bin

The exp export tool compresses data backup in the database into a binary system file, which can be migrated between different operating systems.

It has three modes:
A. User Mode: export data of all user objects and objects;
B. Table mode: export all or specified tables of the user;
C. entire database: export all objects in the database.

2. Example of using the export tool exp interactive command line

$ Exp test/test123 @ appdb
Enter array fetch buffer size: 4096> press Enter
Export file: expdat. dmp> m. dmp generate exported file name
(1) E (ntire database), (2) U (sers), or (3) T (ables): (2) U> 3
Export table data (yes/no): yes> press ENTER
Compress extents (yes/no): yes> press ENTER
Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set
About to export specified tables via Conventional Path...
Table (T) or Partition (T) to be exported: (RETURN to quit)> name of the Table to be exported by cmamenu
.. Exporting table CMAMENU 4336 rows exported
Table (T) or Partition (T) to be exported: (RETURN to quit)> name of the Table to be exported n
Table (T) or Partition (T) to be exported: (RETURN to quit)> press ENTER
Export terminated successfully without warnings.

3. Example of export tool exp in non-interactive Command Line Mode

$ Exp scott/tiger tables = (emp, dept) file =/directory/scott. dmp grants = y

Note: The emp and dept tables in the scott user are exported to the file/directory/scott. dmp.

$ Exp scott/tiger tables = emp query = "" where job = "'salesman" 'and sal "<1600" file =/directory/scott2.dmp

Note: add the query condition job = 'salesman' and sal for exporting emp to exp <1600

(But I am rarely using this method. It is more convenient to generate a temporary table for the records meeting the conditions, and then use exp)

$ Exp parfile = username. par file =/directory1/username_1.dmp,/directory1/username_2.dmp filesize = 2000 M log =/directory2/username_exp.log

Parameter file username. par content
Userid = username/userpassword
Buffer= 8192000
Compress = n
Grants = y

Description: username. par is the parameter file used for exporting tool exp. The specific parameters can be modified as needed.

Filesize specifies the maximum number of bytes of the generated binary backup file

(It can be used to solve the limitation of 2 GB physical files in some operating systems, accelerate the compression speed, and facilitate the engraving of historical data CDs)

Ii. Import tool imp

1. It is the directory for storing the next executable file in the operating system/ORACLE_HOME/bin

The imp import tool imports binary system files generated by EXP into the database.

It has three modes:
A. User Mode: export data of all user objects and objects;
B. Table mode: export all or specified tables of the user;
C. entire database: export all objects in the database.

Only users with IMP_FULL_DATABASE and DBA permissions can import the entire database.

Imp steps:
(1) create table (2) insert data (3) create index (4) create triggers, constraints

2. Import tool imp interactive command line method example
$ Imp
Import: Release 8.1.6.0.0-Production on Friday December 7 17:01:08 2001
(C) Copyright 1999 Oracle Corporation. All rights reserved.
User name: test
Password :****
Connect to: Oracle8i Enterprise Edition Release 8.1.6.0.0-64bit Production
With the Partitioning option
JServer Release 8.1.6.0.0-Production
Import file: expdat. dmp>/tmp/m. dmp
Input buffer size (minimum 8192) 30720>
EXPORT files created by EXPORT: V08.01.06 in the normal path
Warning: This object is exported by TEST instead of the current user.
The ZHS16GBK Character Set and ZHS16GBK NCHAR character set have been imported.
Only list the content of the imported file (yes/no): no>
The creation error is ignored because the object already exists (yes/no): no> yes
Import Permission (yes/no): yes>
Import table data (yes/no): yes>
Import the entire exported file (yes/no): no> yes
. Importing the TEST object to SCOTT.
.. Importing table "CMAMENU" 4336 rows being imported
Import is terminated successfully, but a warning is displayed.

3. Import tool imp non-interactive command line method example

$ Imp system/manager fromuser = jones tables = (accts)
$ Imp system/manager fromuser = scott tables = (emp, dept)
$ Imp system/manager fromuser = scott touser = joe tables = emp
$ Imp scott/tiger file = expdat. dmp full = y
$ Imp scott/tiger file =/mnt1/t1.dmp show = n buffer = 2048000 ignore = n commit = y grants = y full = y log =/oracle_backup/log/imp_scott.log
$ Imp system/manager parfile = params. dat
Params. dat content
File = dba. dmp show = n ignore = n grants = y fromuser = scott tables = (dept, emp)

4. Problems with the import tool imp

(1) The database object already exists.
Generally, tables, sequences, functions/processes, and triggers under the target data should be completely deleted before data is imported;
The database object already exists. If you use the default imp parameter, the Import fails.
If the ignore = y parameter is used, the data content in the exp file will be imported.
If the table has a constraint that contains a unique keyword, the table will not be imported if the condition is not met.
If the table does not have a constraint for a unique keyword, record duplication occurs.

(2) database objects are subject to primary and foreign key constraints
Data Import fails if it does not comply with the primary and foreign key constraints.
Solution: import the dependency table first.
The primary and foreign key constraints of the disable object to be imported. After the data is imported, enable them
(3) Insufficient Permissions
If you want to import user A's data to user B, user A must have the imp_full_database permission.

(4) failed to allocate storage when importing large tables (greater than 80 M)
Compress = Y for the default EXP, that is, compress all data into one data block.
If there is no continuous big data block during import, the import will fail.
When exporting a large table larger than 80 Mb, remember to compress = N, and this will not cause this error.

(5) imp AND exp use different character sets.
If the character set is different, the import will fail. You can change the unix environment variable or the information about NLS_LANG in the NT Registry.
After the import is complete, change it back.

(6) imp AND exp versions cannot be compatible
Imp can successfully import files generated by earlier exp versions. Files generated by later exp versions cannot be imported.
We can use
$ Imp username/password @ connect_string
Connect_string is in/ORACLE_HOME/network/admin/tnsnames. ora
Name of the defined local or remote database
Note:
UNIX:/etc/hosts to define the Host Name of the local or remote database server
Win98: ing between windows "hosts and IP address

Win2000: winnt "system32" drivers "etc" hosts

 

 

Additional instructions on Oracle Database Export (exp)/import (imp)

 

Exp
The database objects are downloaded as dmp files in binary format to facilitate data migration.
Buffer: the buffer for downloading data, in bytes. It is dependent on the operating system by default.
Consistent: the data involved during the download process is read only. The default value is n.
Direct: Use the pass-through method. The default value is n.
Feeback: displays the number of processing records. The default value is 0, that is, it is not displayed.
File: output file. The default value is expdat. dmp.
Filesize: size of the output file. The default value is the maximum value of the operating system.
Indexes: whether to download the index. The default value is n, which indicates the index definition rather than data. exp does not download the index data.
Log: log file. The default value is none. It is displayed in standard output.
Owner: Specifies the downloaded user name.
Query: select a subset of records.
Rows: Indicates whether to download table records.
Tables: List of output table names
Export the entire instance
Exp dbuser/oracle file = oradb. dmp log = oradb. log full = y consistent = y direct = y
The user shall have dba Permissions
Export all objects of a user
Exp dbuser/oracle file = dbuser. dmp log = dbuser. log owner = dbuser buffer = 4096000 feedback = 10000
Export one or more tables
Exp dbuser/oracle file = dbuser. dmp log = dbuser. log tables = table1, table2 buffer = 4096000 feedback = 10000
Export some data of a table
Exp dbuser/oracle file = dbuser. dmp log = dbuser. log tables = table1 buffer = 4096000 feedback = 10000 query = "" where col1 = "'... "'And col2" <... ""
Cannot be used for nested tables
Export a table using multiple fixed-size files
Exp dbuser/oracle file = 1.dmp, 2.dmp, 3.dmp ,... Filesize = 1000 m tables = emp buffer = 4096000 feedback = 10000
This method is usually used when the table data volume is large and a single dump file may exceed the limit of the file system.
Pass-through path
Direct = y, replace the buffer option, and the query option is unavailable
Improves download speed
Consistent options
After the export is started, consistent = y freezes updates to the Data Objects of the export operation from other sessions, so as to ensure the consistency of the dump results. However, this process cannot be too long to prevent the rollback segment and the online log consumption from being completed.
Imp
Upload the dmp file downloaded by exp to the database.
Buffer: Upload data buffer, in bytes, which is dependent on the operating system by default
Commit: whether to submit the uploaded records in the data buffer.
Feeback: displays the number of processing records. The default value is 0, that is, it is not displayed.
File: input file. The default value is expdat. dmp.
Filesize: size of the input file. The default value is the maximum value of the operating system.
Fromuser: Specifies the source user
Ignore: whether to ignore the object creation error. The default value is n. It is normal that the object has been created before upload. Therefore, we recommend that you set this option to y.
Indexes: whether to upload an index. The default value is n, which indicates the index definition rather than data. If the index has been created during the upload, this option is invalid even if it is n. imp automatically updates the index data.
Log: log file. The default value is none. It is displayed in standard output.
Rows: whether to upload table records
Tables: List of input table names
Touser: Specify the target user
Import the entire instance
Imp dbuser/oracle file = oradb. dmp log = oradb. log full = y buffer = 4096000 commit = y ignore = y feedback = 10000
Import all objects of a user
Imp dbuser/oracle file = dbuser. dmp log = dbuser. log fromuser = dbuser touser = dbuser2 buffer = 2048000 commit = y ignore = y feedback = 10000
Import one or more tables
Imp dbuser2/oracle file = user. dmp log = user. log tables = table1, table2 fromuser = dbuser touser = dbuser2 buffer = 2048000 commit = y ignore = y feedback = 10000
Import a table using multiple fixed-size files
Imp dbuser/oracle file = "(1.dmp, 2.dmp, 3.dmp ,... ") Filesize = 1000 m tables = emp fromuser = dbuser touser = dbuser2 buffer = 4096000 commit = y ignore = y feedback = 10000