Using exp/imp commands in Oracle to quickly import and export data

Use the expimp command in Oracle to quickly import and export data [export data with exp]: 1. Export the database TEST completely. Use the User Name system Password manager to export data to D: daochu. in dmp, expsystemmanager @ TESTfiled: daochu. dmpfully2 exports the tables of system users and sys users in the database to expsystemman.

Use the exp/imp command in Oracle to quickly import and export data [export data using exp data]: 1 completely export the database TEST, and export the username system Password manager to D: \ daochu. exp system/manager @ TEST file = d: \ daochu in dmp. dmp full = y 2 export the tables of system users and sys users in the database to exp system/man

Using exp/imp commands in Oracle to quickly import and export data

[Export with exp data ]:

1. Export the database TEST completely, and the username system Password manager is exported to D: \ daochu. dmp.
ExpSystem/manager @ TESTFile = 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 headers starting with "00" for the field filed1 in table 1 in the database.
Exp system/manager @ TEST file = d: \ daochu. dmp tables = (table1) query = \ "where filed1like '000000 '\"

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.

[Import data using imp ]:

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.

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.
(Add the correct service name through the local-> service name in net8 assistant.
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.

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.

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

For more information, see:

1. Export tool exp

1. It is the next executable file in the operating system.Storage directory/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 arrayfetch buffer size: 4096> 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
.. Exportingtable 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 \ 'andsal \ <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)

$ Expparfile = username. par file =/directory1/username_1.dmp,/directory1/username_2.dmpfilesize = 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 = ygrants = 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.Import tool imp Problems

(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.

At this time,
If the (target) Table [that is, the table to be imported] has a constraint that has a unique keyword, the table will not be imported if it does not meet the conditions.
If the (target) Table (that is, the table to be imported) does not have a constraint for the unique keyword, the record will be repeated.

(2)The database object has 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.
(Or) 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 in oneDataBlock.
If there is no continuous big data block during import, the import will fail.
When exporting a large table larger than 80 Mb, (e x p) remembers compress = N, which 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

EXP can be exported to multiple DUMP files.

Many DBAs do not realize that Export can use multiple data files to store exported data. If a single disk volume does not have enough space or the database exceeds the size limit of a single file in the operating system, it is easy to split the exported data into multiple files.

As early as Oracle 8i, the FILE parameter can accommodate multiple FILE paths separated by commas. The FILESIZE parameter allows you to specify how much data can be written to a single file before it is transferred to the next file. If the Export uses up the name in the FILE list, it will prompt an additional FILE name.

For example, let's assume that all database export requires 6 GB space and the size of a single file in the operating system is limited to 2 GB. You need to put the exported file in the prod directory of the/exp file system. This parameter file should contain the following content:

FILE =/exp/prod/exp01.dmp,/exp/prod/exp02.dmp,/exp/prod/exp03.dmp FILESIZE = 2G

In Oracle 10 Gb, the usage of the new Data Pump export (expdp) is similar but slightly changed. The output file path is no longer written as in earlier versions. Instead, the Directory object is used to indicate the Directory in the operating system. The FILE parameter is replaced by the DUMPFILE parameter. For ease of use, you can specify wildcards to automatically generate FILE names instead of all columns.

In Oracle 10 Gb, if you want to create a Directory named EXPDIR pointing to the/exp/prod Directory, the parameters in the above example will become like this:

DUMPFILE = expdir: exp % U. dmp FILESIZE = 2G

Exp01.dmp, exp02.dmp, and exp03.dmp are automatically generated during the Export process.

Category: Database (ORACLE, SQLSERVER)