Step by Step Oracle Data Import Export

Oracle Data Import Export imp / exp on oracle is equivalent to restore and backup data. exp command to put the data from a remote database server to the local exported dmp file, imp command dmp files can be put into the distance from the local database server. This function can be build using the same two databases, one for testing, one for official use.

Execution Environment: SQLPLUS.EXE or in DOS (command line) in the implementation of
DOS can be implemented in oracle 8i because of the installation directory is set to ora81BIN global path,
EXP.EXE the directory has been used with the implementation of IMP.EXE document import and export.
oracle using java prepared, SQLPLUS.EXE, EXP.EXE, IMP.EXE have the two documents may have been the type of paper packaging.
SQLPLUS.EXE call EXP.EXE, IMP.EXE wrapped by the class to complete import and export functions.

Here is an example of import and export.
Data Export:
1 will be derived entirely TEST database, user name, system password manager Export to D: daochu.dmp Medium
exp system / manager @ TEST file = d: daochu.dmp full = y
2 will be system users database table and export sys user
exp system / manager @ TEST file = d: daochu.dmp owner = (system, sys)
3 will be in the database table inner_notify, notify_staff_relat Export
exp aichannel / aichannel @ TESTDB2 file = d: datanewsmgnt.dmp tables = (inner_notify, notify_staff_relat)

4 will be in the database field in the table1 table filed1 to "00" Export data from the beginning
exp system / manager @ TEST file = d: daochu.dmp tables = (table1) query = "where filed1 like'00% '"

Derived above are commonly used for compression, not only put dmp file with winzip compression can be very good.
In the above command can also be followed by compress = y to achieve.

Data import
1 will be D: daochu.dmp in the TEST data into the database.
imp system / manager @ TEST file = d: daochu.dmp
imp aichannel / aichannel @ HUST full = y file = d: datanewsmgnt.dmp ignore = y
Above problem may be a bit, because some form already exists, then it is error, not to import the table.
Behind with ignore = y on it.
2 d: daochu.dmp import table table1
imp system / manager @ TEST file = d: daochu.dmp tables = (table1)

Basically, the above import and export good enough for us. A number of cases is to first delete the complete table, and then import.

Note:
The operator must have enough permissions, it will prompt not enough permissions.
Can be connected to the database of. Tnsping TEST can be used to access the database can connect to the TEST.

Appendix 1:
Import data to the user to increase the operation of the first authority to start the sql * puls
Second, system / manager landed third, create user username IDENTIFIED BY password (if the user has been created, this step can be omitted)
Fourth, GRANT CREATE USER, DROP USER, ALTER USER, CREATE ANY VIEW,
DROP ANY VIEW, EXP_FULL_DATABASE, IMP_FULL_DATABASE,
DBA, CONNECT, RESOURCE, CREATE SESSION TO user name of the fifth, run-cmd-enter the dmp file directory,
imp userid = system / manager full = y file =*. dmp
Or imp userid = system / manager full = y file = filename.dmp

Implementation of examples:
F: WorkOracle_Databackup> imp userid = test / test full = y file = inner_notify.dmp

Screen display
Import: Release 8.1.7.0.0 - Production on Thursday, February 16 16:50:05 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connect to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

Derived from the conventional path by EXPORT: V08.01.07 documents have been created ZHS16GBK achieve ZHS16GBK NCHAR character set and character set import server uses UTF8 NCHAR Export Character Set (ncharset the possible conversion)
. AICHANNEL the object being imported into AICHANNEL
.. Is to import table "INNER_NOTIFY" 4 lines are ready to import the opening of constraints ...
Successful termination of import, but a warning.

Appendix Two:
Oracle does not allow direct changes in the owner table, using Export / Import can achieve this purpose.
First set up import9.par,
Then, use the following command: imp parfile = / filepath/import9.par
Example import9.par reads as follows:
FROMUSER = TGPMS
TOUSER = TGPMS2 (Note: the owner of the table FROMUSER replaced by TOUSER, FROMUSER and different users can TOUSER)
ROWS = Y
INDEXES = Y
GRANTS = Y
CONSTRAINTS = Y
BUFFER = 409600
file == / backup/ctgpc_20030623.dmp
log == / backup/import_20030623.log