Oracle data import and export imp / exp on the equivalent of Oracle data restore and backup. exp command to export data from a remote database server to the local dmp file, imp command to import the dmp file from the local to the remote database server. With this feature you can build two identical databases, one for testing, one for official use.
Execution environment: in SQLPLUS.EXE or DOS (command line) to perform
DOS can be performed due to the to installation directory ora81BIN oracle 8i is set to the global path,
The directory the EXP.EXE IMP.EXE file is used to perform import and export.
oracle using java prepared the two files SQLPLUS.EXE EXP.EXE IMP.EXE may be packaged class files.
SQLPLUS.EXE call EXP.EXE the wrapped class IMP.EXE, to complete the import and export functions.
The following describes the instance of import and export.
Data Export:
1 full export of the database TEST, user name, password manager system exported to D: daochu.dmp the
exp file = d: daochu.dmp full = y
The database system user and sys users export a table
exp file = d: Owner of daochu.dmp = (system, sys)
3 tables in the database inner_notify, notify_staff_relat export
The the exp file = d: tables of datanewsmgnt.dmp = (inner_notify notify_staff_relat)
4 tables in the database table1 the field filed1 to “00” starts with the data export
the exp file = d: tables of daochu.dmp = (table1) query = “of where filed1 like ‘00% ‘”
The above is commonly used export compression winzip dmp file compression.
Can also be in the above command is followed by plus compress = y to.
Data import
1 D: daochu.dmp the data import TEST database.
the imp file = d: daochu.dmp
the imp full = y file = file = d: datanewsmgnt.dmp ignore = y
Above may be a little problem, because some table already exists, then it is an error, the table will not be imported.
In the back plus ignore = y.
2 d: daochu.dmp the table table1 import
the imp file = d: tables of daochu.dmp = (table1)
Basically at the import and export enough. Many first completely remove the table, and then import.
Note:
Operator to have sufficient permissions, the permission is not enough, it will prompt.
Can connect to the database. Can be used to the database test whether connected tnsping TEST.
Appendix 1:
Operations increased import the data to the user permissions
First, start SQL * puls
Second, log in as system / manager
Third, create user username IDENTIFIED BY password (If you have created a user, 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
Fifth, the run-cmd-enter the directory where the dmp file
imp userid = system / manager full = y file = *. dmp
Imp userid = system / manager full = y file = filename.dmp for
Execution Example:
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, 2006 16:50:05
(C) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 – Production
With the Partitioning option
JServer Release 8.1.7.0.0 – Production
Export file created by EXPORT: V08.01.07 via conventional path has been completed ZHS16GBK character set ZHS16GBK NCHAR character set import and export server uses UTF8 NCHAR character set (may ncharset conversion)
Being AICHANNEL objects into the AICHANNEL
.. Being imported table INNER_NOTIFY “4 rows imported to enable constraints …
Import terminated successfully with warnings.
Appendix II:
Oracle does not allow directly change the owner of a table, use the Export / Import can achieve this goal.
First create import9.par,
Then, use the command as follows: the imp parfile = / filepath/import9.par
The cases import9.par reads as follows:
FROMUSER = TGPMS
The users can TOUSER = TGPMS2 (Note: The owner of a table by FROMUSER to TOUSER, FROMUSER and TOUSER)
ROWS = Y
INDEXES = Y
GRANTS = Y
CONSTRAINTS = Y
BUFFER = 409600
file == / backup/ctgpc_20030623.dmp
log == / backup/import_20030623.log
