[Summary] Import and Export of Oracle Data imp/exp

Function: Oracle Data Import/Export imp/exp is equivalent to Oracle data restoration and backup.
In most cases, you can use ORACLE data import and export to back up and restore data (without causing data loss ).

Oracle has a benefit. Although your computer is not a server, you have installed an Oracle client and established a connection.
(Use the local -- & gt; service name in net8 assistant to add the correct service name
In fact, you can think that the client and the server have built a path, and then the data can be pulled)
In this way, you can export data locally, although the server may be far away from you.
You can also import the DMP file locally to a database server in a distance.
With this function, you can build two identical databases, one for testing and the other for formal use.

Execution environment: it can be executed in sqlplus. EXE or DoS (command line,
When dos can be executed, the \ ora81 \ bin installation directory in Oracle 8i is set to a global path,
The exp. EXE and imp. EXE files in this directory are used for import and export.
Oracle is written in Java. I think the sqlplus. EXE, exp. EXE, and imp. EXE files are packaged class files.
Sqlplus. EXE calls exp. EXE and imp. EXE to complete the import and export functions.

The following describes the Import and Export instances. You can import and export instances to the Import and Export instances, because the import and export operations are very simple.
Data export:
1. Export the database test completely, and the username System Password Manager is exported to D: \ daochu. dmp.
Exp system/manager @ test file = D: \ daochu. dmp full = y
2. Export the tables of system users and SYS users in the database
Exp system/manager @ test file = D: \ daochu. dmp owner = (system, sys)
3. Export tables Table1 and Table2 in the database
Exp system/manager @ test file = D: \ daochu. dmp tables = (Table1, table2)
4. Export the data with the field filed1 in table 1 in the database starting with "00"
Exp system/manager @ test file = D: \ daochu. dmp tables = (Table1) query = \ "where filed1 like '201312 '\"

The above is a commonly used export. I am not very concerned about compression. I can use WinZip to compress the DMP file.
However, add compress = Y to the command above.
Data Import
1. import data from D: \ daochu. dmp to the test database.
IMP system/manager @ test file = D: \ daochu. dmp
The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.
Add ignore = Y to the end.
2. Import table 1 in D: \ daochu. dmp
IMP system/manager @ test file = D: \ daochu. dmp tables = (Table1)

The preceding import and export operations are sufficient. In many cases, I completely delete the table and then import it.

Note:
If you have sufficient permissions, the system will prompt you.
Databases can be connected. You can use tnsping test to obtain whether the database test can be connected.

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: p) 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: p) to be exported: (return to quit)> name of the table to be exported n
Table (t) or partition (T: p) 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: Relationship between Windows \ hosts and IP address

Win2000: winnt \ system32 \ drivers \ etc \ hosts